Numeric only?

# Thread: Numeric only?

1. Join Date
Dec 1969
Posts
82

## Numeric only?

In one table I have a phone number field with a bunch of ()- in the data. In another table I have a phone number field with only numeric digits. Is there a way when comparing these two fields to ignor the unwanted characters from the first tables phone number field? (We use SQL Server 2000)

2. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: Numeric only?

replace(replace(replace(&#060;field&#062;, &#039;(&#039;, &#039;&#039;), &#039;)&#039;, &#039;&#039;),&#039;-&#039;, &#039;&#039;)<BR><BR>I&#039;d suggest you Standardise your telephone number format1

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## Replace???

WHERE table1.Replace(Replace(Replace(Replace(phone,&#039 ;(&#039;,&#039;&#039;),&#039;)&#039;,&#039;&#039;) ,&#039;-&#039;,&#039;&#039;),&#039; &#039;,&#039;&#039;) = table2.phone<BR><BR>You might want to try doing<BR><BR>SELECT phone FROM table1 WHERE NOT IsNumeric( Replace(Replace(Replace(Replace(phone,&#039;(&#039 ;,&#039;&#039;),&#039;)&#039;,&#039;&#039;),&#039 ;-&#039;,&#039;&#039;),&#039; &#039;,&#039;&#039;) )<BR><BR>first. That will find any cases not covered by replacing the ( ) - and space characters, so just add the offending characters to the list of Replace&#039;s?<BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Wow, almost 2 minutes faster!

But I&#039;ll take credit for the IsNumeric test so he can find characters he missed.<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
11,247

## AND

I take credit for saying standardise the format across the tables

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## heh! should go w/o saying...

...but you are right. Insist on numeric chars only. It&#039;s easy enough to reformat it to (xxx) xxx-xxxx or any other format on the way out of the DB.<BR><BR><BR>

7. Join Date
Dec 1969
Posts
82

## RE: Wish I could

Believe you me when I say I wish I could do that. But the software we use doesn&#039;t allow restrictions on the data entered by the users to do this, and can&#039;t format it on the way out to make it look pretty either. So I have to work with what I got.<BR><BR>Anyways thanks for responding, much appreciated :D

8. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: Wish I could

SQL Server does not allow you to do it? -- oohhh come on its just a matter of strings!

9. Senior Member
Join Date
Dec 1969
Posts
96,118

## WK means...

...that you could create a TRIGGER on that table and do the fixup in the trigger. Then it doesn&#039;t matter whether the data entry software allows junk, as you&#039;ve taken control at the lower level.<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•