0

I have 2 columns, say dialer_code and country_code. I need to check whether the dialer_code matches with the country_code or not.

Example: dialer_code = 1234567890, so it will check whether 1234567890 in country_code matches any country_code entry. If not then it will remove the last digit (turning the value into 123456789) and check again. This repeats until only the first 2 digit are left.

dialer_code    country_code     country_name
190095               91         India
190098               44         UK
4456                 20         Egypt
445678               1900       US

In the above example, 190095 will be checked in country_code, but we don't have anything matching with it so now it will check for country_code 19009, but we don't have anything matching it either, until we get to 1900. Now it's matching with US. So US has some call rate defined per second so it will return that value.

Pawel Veselov
  • 3,996
  • 7
  • 44
  • 62
Akshay
  • 25
  • 6
  • 2
    Please provide sample data and desired results. – Gordon Linoff Jun 11 '21 at 14:01
  • Sample data: dialer_code = 1900590. So, it will check 1900590 in the country_code column and 1900590 does not belong to any country code or any regions so it will check for 190059 in country_code till it will not find any matching column else it will take first 2 digits and we have country whose country code is 19 – Akshay Jun 11 '21 at 14:09
  • first, check for the length of the codes before the actual value. will it simplify the logic? – gowridev Jun 11 '21 at 14:13
  • that we can use while initializing the value of the variables that we will assign – Akshay Jun 11 '21 at 14:22
  • [Edit] your question with the sample data, @Akshay , and include multiple examples along with the expected results. – Thom A Jun 11 '21 at 14:23
  • I mean, if you know the first 2 digits are country code. you can use regex logic. for more info check this [one](https://stackoverflow.com/questions/4511945/select-values-that-begin-with-a-number) – gowridev Jun 11 '21 at 14:26
  • Apart from that SQL Server does *not* support Regex, @gowridev , just simple pattern matching. – Thom A Jun 11 '21 at 14:28
  • 1
    Required reading: [Falsehoods Programmers Believe About Phone Numbers](https://github.com/google/libphonenumber/blob/master/FALSEHOODS.md) – Charlieface Jun 11 '21 at 14:55

1 Answers1

1

Assuming the dilaer_code contains country_code, you could use SUBSTRING, like this:

SUBSTRING(dialer_code, CHARINDEX(country_code, dialer_code), 100)
Yair Maron
  • 1,860
  • 9
  • 22