2

I have a large table with a column containing phone numbers that are formatted inconsistently. i.e 01234567890 or possibly 01234 567 890.

I'm looking for a select statement that will return the record as long as the user search contains the numbers in the correct order regardless of spacing of the record in the database.

So if the user search using 0123456789 it would return the record containing 01234 567 890 or vice versa.

Currently using like but not working as I'd like. Any ideas?

SELECT * 
FROM contacts
WHERE telephone LIKE '%01234567890%
Tunboy
  • 35
  • 1
  • 1
  • 4

6 Answers6

1

Replace() should work for you.

WHERE REPLACE(telephone,' ','') = 01234567890
wvdz
  • 16,251
  • 4
  • 53
  • 90
1

I would suggest removing spaces and other characters before doing the comparison:

SELECT * 
FROM contacts
WHERE replace(replace(replace(replace(telephone, ' ', ''), '(', ''), ')', ''), '-') LIKE '%01234567890%';

This gets rid of spaces, parentheses and hyphens.

You could also do this by fixing the pattern:

where telephone like '%0%1%2%3%4%5%6%7%8%9%0%'

The wildcard % can match zero or more characters, so it would find the numbers in the right order.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Check out this thread here : T-SQL trim &nbsp (and other non-alphanumeric characters)

There is a function listed there as CREATE FUNCTION [dbo].[fnRemoveBadCharacter] that I've used a few times (you'll want to alter it so the characters are no longer allowed). It will provide a function that will remove all the funky formatting in a string for you and leave just 0-9. then do you like statement on the newly formatted field:

where fnRemoveBadCharacter(telephone) like '%18005550090%'
Community
  • 1
  • 1
Twelfth
  • 7,070
  • 3
  • 26
  • 34
  • popovitsj answer will work if you have spaces only...I'll defer to his answer there. If you have -'s or () or other crap people put into free text forms, then I'd try this solution. – Twelfth Aug 21 '14 at 23:16
0

use the REPLACE function

SELECT * FROM contacts WHERE REPLACE(telephone, ' ', '') LIKE '%01234567890%

tofu
  • 197
  • 5
0

Try this:

WHERE REPLACE(telephone, ' ', '') LIKE '%01234567890%'
AstroCB
  • 12,337
  • 20
  • 57
  • 73
Ruslan Veselov
  • 337
  • 1
  • 10
0

In the long run, It might be good to remove spaces in that column using update and also take care if the spaces during insert, so that performance would be improved during select as it is mentioned that table is large

radar
  • 13,270
  • 2
  • 25
  • 33