Query or Function in SQLServer

I have a DB in SQLServer in which there is a column called "Cell phones", the problem with that was that users entered their phone in two ways:

Form A: 0351-153111222. In this case I need to remove the 0, remove the - and remove the 15, leaving as final form 3513111222

Form B: 153111222. In this case I need to remove the 15 and add at the beginning the 351 leaving as final form 3513111222

As I do not want to update the entire table, I wanted to know if there is any query or function that allows me to "accommodate" or "fix" those forms so that they look like this:

Shape I want it to look like: 3513111222

1 answer

  • answered 2019-11-08 13:54 Gordon Linoff

    The logic in SQL would be:

    update t
        set phone = (case when phone like '0%-15%'
                          then stuff(replace(phone, '-15', ''), 1, 1, '')
                          when phone like '15%'
                          then stuff(phone, 1, 2, '')
                          else phone
        where phone like '0%-15%' or phone like '15%';

    This is a one-time fix though. You should fix your data ingestion process to ensure that numbers are in the format you want.