CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Pramod Mangalore
Pramod Mangalore

Reputation: 536

Replace ONLY the first occurrence of a string in SQL

I have to update all phone numbers that start with 00 by replacing the 00 with a '+' prefix. I must replace only the first occurrence of 00 and nothing else.

phone_number:     000258843300081
ActualResult:     +02588433+081
ExpectedResult:   +0258843300081

Incorrect Query Used:

    UPDATE [dbo].[phone]
    SET phone_number = REPLACE(phone_number, '00', '+')
    WHERE phone_number_numeric LIKE '00%'

Upvotes: 2

Views: 6197

Answers (2)

Pramod Mangalore
Pramod Mangalore

Reputation: 536

Here's the correct query. Just convert to an update command! Also, I've used a generic approach to solve the problem.

    SELECT 
      phone_number
    , STUFF(phone_number, CHARINDEX('00', phone_number), LEN('00'), '+') as ExpectedResult
    FROM dbo.phone 
    WHERE phone_number_numeric LIKE '00%'

The REPLACE function looks at the entire string. Use STUFF function to narrow the scope down to the desired limit.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Just use STUFF():

UPDATE [dbo].[phone]
    SET phone_number = STUFF(phone_number, 1, 2, '+')
    WHERE phone_number_numeric LIKE '00%';

Upvotes: 3

Related Questions