Searching for a number not stored as a pure number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Searching for a number not stored as a pure number

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-11 : 07:32:03

Hem writes "Hi,

Most telephone number fields are created using a string type. This allows for free formatting of the telephone number when entering it through a GUI.

The problem is searching for a specific telephone number, in the most efficient way.

For example, if I was searching for the telephone number 00442081234, I would not be able to do an exact match search as the number stored in the database may contain non-numerical characters. These non-numerical characters could also be anywhere in the number. So the telephone number could be stored as...

00 44 208 1234
0044 (208) 1234

etc...

This is my dilemma. What would you say was the most efficient way of searching? Is there an SQL function that could help?

Thank you."

jpiscit1
Posting Yak Master

130 Posts

Posted - 2005-05-11 : 08:04:32

This will work...right?

Select *
From tbl_TestTable
Where test4 Like '00%44%208%1234%'

Successfully finds results for the following:

Scenario 1 = 0044(208)1234
Scenario 2 = 00442081234
Scenario 3 = 0044 208 1234
Scenario 4 = 0044 2081234
Scenario 5 = 0044-208-1234
Scenario 6 = 00 44 208 1234

Your limits are only as far as you set your boundries....

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-11 : 08:37:02

You should really just store the number(string), and leave the formatting to the client. Really. Make the GUI submitt ONLY number characters.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources