CODEWITHSUNDEEP
c#sqlasp.netsql-server
Ratna
Ratna

Reputation: 2319

How to search a string(Phone number) which can be stored in different format in mssql

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?

Upvotes: 0

Views: 1960

Answers (4)

Matt
Matt

Reputation: 15061

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

Upvotes: 0

JotaBe
JotaBe

Reputation: 39045

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.

Upvotes: 3

Veera
Veera

Reputation: 3492

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')+'%'

Upvotes: 0

Andrey Korneyev
Andrey Korneyev

Reputation: 26876

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.

Upvotes: 0

Related Questions