Interesting Phone Number Question

[KingKea]

I am currently trying to identify bad phone number in a database based on the exchange in relation to the area code.
Normally I will parse out the portion of the phone number -left(phone,7) and then do the comparision IN a list of the areacode+exchange.

However on occasion I am having to deal with some legacy information stored as text string in multiple field of a table. This limits using a direct lookup since I don't alway know where the number is going to be.

To resolve this I will concantenate the fields and search for the string using a like statement

line1+line2+line3+line4 like '%949[/-)]941-%'

In some case it will be a few hundred exchanges that are invalid in an area code, this means a few hundred lines.

Is there a cleaner way of doing this something along the lines of a IN statement within a LIKE statement?

 

[ESquared]

Do a join to a table which contains the prefixes and such instead of putting all that information in the where clause.

You can join then using LIKE in your join condition.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[KingKea]

I just caught your response.

When you say:

'You can join then using LIKE in your join condition'

what exactly do you mean?

 

[ESquared]

CREATE TABLE BadCodePrefix (
   BadPattern varchar(20) NOT NULL PRIMARY KEY CLUSTERED
)

INSERT BadCodePrefix VALUES ('%949[/-)]941-%')
INSERT BadCodePrefix VALUES ('%949[/-)]942-%')

SELECT *
FROM
   MyTableToCheck T
   INNER JOIN BadCodePrefix B ON T.line1+line2+line3+line4 LIKE B.BadPattern

-- also if performance matters, compare to alternate version.

SELECT *
FROM
   MyTableToCheck T
   INNER JOIN BadCodePrefix B ON
T.line1 LIKE B.BadPattern
OR T.line2 LIKE B.BadPattern
OR T.line3 LIKE B.BadPattern
OR T.line4 LIKE B.BadPattern

If the task will be repeated or the table is very large, try adding an index on line1 - line 4 and removing the leading % from BadPattern column if at all possible. Even adding the index temporarily can help enormously. Experiment. Sometimes adding the index, performing the query, and then dropping the index is superior to any other data access pattern.

 

[KingKea]

Thanks - I didn't know that you could use wildcards in an inner join like that ...

 

[ESquared]

You can join on almost anything you want including case statements, functions, and complicated math. Just be aware that when there are no equijoins (at least one join condition using an equal sign) you're pretty much condemned to a hash join.

Wait... not sure about the functions.