0

I am having a table of customers where we are having phone number field defined as varchar(50). Since there is no validation different customers have saved there phone number in their own preferred way eg

cust1 --> xxx-xxx-xxx

cust2 --> (xxx)xxx-xxx

cust3 --> xxx xxx xxx

this hasn't created any problem till now because no one was searching using phone number. After adding search functionality, the client wasn't able to get the desired result because to match you also have to provide the query in same format.

Is there any way I can search the phone column considering only numbers?

bummi
  • 27,123
  • 14
  • 62
  • 101
Ratna
  • 2,289
  • 3
  • 26
  • 50
  • are you using any ORM or direct queries? Either way you can write sql function which will compare numbers using a regex or simply removing no applicable characters from phone string – Guru Stron Dec 12 '14 at 11:05
  • 1
    You could strip out the unwanted chars by replacing them with "" using REPLCAE http://msdn.microsoft.com/en-us/library/ms186862.aspx – Ben Robinson Dec 12 '14 at 11:07
  • How many rows of data do you have? – Matt Dec 12 '14 at 11:21

4 Answers4

3

My answer was going to be very similar to the one by Andy Korneyev, but I'm going to add some extra details.

WHY A COLUMN WITH NORMALIZED PHONE NUMBERS?

If you have a lot of rows in your table, it's advisable to have a column with a normalized format, so that, when your user wants to look for a phone number, the application normalizes it and looks for it.

If you use any of the solution to query by normalizing the user entry as well as the value in the table column your server has to do extra work, and there is not a chance to use indexes.

So, the best solution by far is having that normalized column.

This column can be created directly from the application, or apply triggers to the original table to create the normalized column (I personally wouldn't use triggers).

WHAT TO DO ABOUT PREFIXES TO KEEP USING THE INDEX?

And finally, there is a typical case when looking for phone numbers: sometimes people include the prefix, sometimes not. So you would have to look with a predicate like this WHERE T.PhoneNumber LIKE '%5551234'. If you do so, you wouldn't get any benefit if your phone number column is indexed.

To solve this typical problem the number is stored normalized and reversed, i.e. the previous sample would be stored like this: '4321555', or '4312555070', for example, if there was a prefix.

Normalization apart (i.e. remotion of non digits), when your user looks for '5551234' or even for '0705551234', the application can reverse it, and use a predicate like this:

WHERE T.PhoneNumber LIKE '4321555%' OR '4321555' LIKE T.PhoneNumber+'%'

The first part covers the case when the number in the DB has prefix, and the user doesn't specify it. (i.e. the stored phone numer is 4321555070)

The second part covers the opposite case. You'll see it more clearly with this sample (when the stored phone number is 4321555):

WHERE T.PhoneNumber LIKE '4321555070%' OR '4321555070' LIKE T.PhoneNumber+'%'

Thats' why, in many CRMs you'll find a "ReversedPhoneNumber" field.

By the way, when you reverse the number, if there is an index available, it will be used, speeding up the search.

Community
  • 1
  • 1
JotaBe
  • 38,030
  • 8
  • 98
  • 117
  • Oh, don't waste your time. OP have said in comments that he is having 2 millions records in table. But he decided to choose most inefficient solution suggested. ;) – Andrey Korneyev Dec 12 '14 at 11:39
  • 1
    Never lose your hope! There are other people in the world that could find this useful. I sometimes get a +1 for a very old and forgotten question, what makes me happy because it's been usefult for someone. – JotaBe Dec 12 '14 at 11:44
  • @JotaBe your answer is really helpfull and given me a lot of insight that I will use while designing new system, Since the current system is old and getting depriciated it wont be a good idea to put this must effort. Not accepting your answer dosen't mean I dont appreciate your effort, it is just easy for me to go the other way in this case.+1 any way – Ratna Dec 13 '14 at 05:57
  • @Ratna. Thank you. Don't worry, I don't mind if the answer is accepted or not. One of the strong points of SO is that even a non accepted answer can get upvoted and become useful for other people with the same problem: you can find lots of questions with several very interesting answers. Get sure you apply the update to the answer you accepted: it wasn't removing spaces. If you suspect there can be other untreated cases, you can adapt this to your problem: http://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server – JotaBe Dec 14 '14 at 12:06
0

Straightforward solution:

  1. Create another one column which will contain "purified" numbers.
  2. Fill it from existed one having phone numbers by removing brackets, spaces, dashes and so on - leave only digits.
  3. Write after insert, update trigger on your table which will fill that new column from old one.
  4. Build index on that new column.

Now you can remove all "nondigits" from search query and simply use your new column to search in.

Or, if your database is pretty small - you can just remove all "non-digits" in your query "on the fly" - but in this case index will not be used even if you have index built on your phone column.

Andrey Korneyev
  • 26,353
  • 15
  • 70
  • 71
  • Or you can create a Calculated column on the table that will "clean" the phone number as well. Use the function in @NoDisplayName's answer and add a column to your table like shown here http://stackoverflow.com/questions/2596059/how-to-define-a-complicated-computedcolumn-in-sql-server – Jaques Dec 12 '14 at 11:15
0
SELECT customer, phonenum
FROM table
WHERE replace(replace(replace(replace(phonenum,'-',''),'(',''),')',''),' ','') like '%1234%'

Replace the like '%1234%' with whatever parameter you are using to define the search

Matt
  • 14,906
  • 27
  • 99
  • 149
  • 1
    Really not the most efficient query ever. He will only get phone numbers that is *1234* – Jaques Dec 12 '14 at 11:16
  • But very simple to return what he wants without creating new functions or creating extra columns, i suppose it depends what OP wants – Matt Dec 12 '14 at 11:16
  • 1
    Obviously he will only get 1234 that was just an example search, i assume he would replace that with @phonenum or what ever it would be defined as in his code – Matt Dec 12 '14 at 11:17
  • 1
    Its inefficient, and counter productive. because everytime he runs his query, he will wait a year for his data if that column is not indexed, and if he have 20 million records in the table. – Jaques Dec 12 '14 at 11:20
  • 3
    This may be counter productive but has solved my problem, I had 2 million records in order table and it is returning correct values. Thanks matt. – Ratna Dec 12 '14 at 11:25
  • Excellent, how long did it take to run with 2m records? – Matt Dec 12 '14 at 11:26
  • If you're sticking to his inefficient solution, at least, correct it and remove also the spaces `' '`. The sample for "cust3" has spaces in it. If someone introduced any other special character it will not work. I hope you're lucky and don't have to correct this solution over and over or find one day a terrible performance degrading. – JotaBe Dec 12 '14 at 11:52
0

Use the below Fuction to extract INT value from the phone number: 

CREATE FUNCTION ExtractInteger(@String VARCHAR(2000))
RETURNS VARCHAR(1000)
AS
BEGIN
    DECLARE @Count INT
    DECLARE @IntNumbers VARCHAR(1000)
    SET @Count = 0
    SET @IntNumbers = ''

    WHILE @Count <= LEN(@String)
    BEGIN
    IF SUBSTRING(@String,@Count,1) >= '0'
    AND SUBSTRING(@String,@Count,1) <= '9'
    BEGIN
    SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
    END
    SET @Count = @Count + 1
    END

RETURN @IntNumbers
END

Search for Phone number as follows: ('323-111' = '(323)111')

DECLARE @SearchNumber VARCHAR(1000)
SET @SearchNumber = '323-111'
SELECT 'MatchFount' AS RESULT 
 WHERE dbo.ExtractInteger(@SearchNumber) 
    LIKE '%'+ dbo.ExtractInteger('(323)111')+'%'
Veera
  • 3,412
  • 2
  • 14
  • 27