CODEWITHSUNDEEP
sqlsql-server
Alex Salgado
Alex Salgado

Reputation: 35

Replacing strings with integers in SQL Server

I'm trying to replace any phone number in a column that may consist with the letter o instead of the number 0. Is there any way I can do this?

For example: a Phone column that accepts NVARCHAR and there are multiple inputs of numbers like this:

1-800-9o6o
(962)47l-9o8o
(472)1o4-7o91

by multiple I mean 80+

Upvotes: 2

Views: 1348

Answers (3)

Sean Lange
Sean Lange

Reputation: 33581

With a simple replace statement. https://msdn.microsoft.com/en-us/library/ms186862.aspx

Replace(YourColumn, 'o', '0')

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269943

Just use replace():

update t
    set phone = replace(phone, 'o', '0')
    where phone like '%o%';

Given your examples, you might also want to change the lower-case "l" to 1:

update t
    set phone = replace(replace(phone, 'o', '0'), 'l', '1')
    where phone like '%[ol]%';

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39487

You can use replace function:

replace(phone_number, 'o', '0')

If you are selecting, then use:

select replace(phone_number, 'o', '0') from t;

If you need to update the table:

update t
set phone_number = replace(phone_number, 'o', '0')
where phone_number like '%o%';

Upvotes: 0

Related Questions