-1

I have three types of phone numbers in my SQL server table as below

enter image description here

Can someone please suggest how can I perform a search operation for below scenarios.

  1. Search by 10 digit number --- 029700456
  2. Search by 10 digit number that was in the range --- 0294005623
Dale K
  • 25,246
  • 15
  • 42
  • 71
Kidoo
  • 39
  • 3
  • 1
    The second requirement would be hard to come by using SQL Server. – Tim Biegeleisen Aug 08 '20 at 11:05
  • 2
    can you please elaborate the second scenario and provide the expected output on the given input? – sacse Aug 08 '20 at 11:16
  • i would like to retrieve the record when i search for the phone number that was present in the range . for example searching 0294005623 should give me row with (+61) 0294005622-5624 – Kidoo Aug 09 '20 at 04:47
  • 1
    @Kidoo Thanks! I think Gordon's answer does that perfectly, right? – sacse Aug 09 '20 at 05:54

1 Answers1

0

You can convert all the numbers to a canonical format using translate() and replace(). From the canonical format, you can define the upper and lower bound on the range, depending on whether the phone has 10 or 14 characters:

select t.*
from t cross apply
     (values (case when phone like '(+61)%'
                   then stuff(replace(translate(t.phone, '()+-', '    '), ' ', ''), 1, 2, '')
                   else replace(translate(t.phone, '()+-', '    '), ' ', '')
              end)
     ) v(canonical)
     (values (left(canonical, 10),
              (case when len(canonical) = 10 then canonical
                    else left(canonical, 6) + right(canonical, 4)
               end)
              )
     ) v2(phone_upper, phone_lower)

Then your conditions are:

where @phone between v2.phone_lower and v2.phone_upper

I would advise you to figure out how to fix the data model. This is a really, really, really bad way to store phone numbers and phone number ranges.

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