CODEWITHSUNDEEP
sqlsql-server
saran
saran

Reputation: 57

Replace in Sqlserver

how to replace only exact matched word in SqlServer.

please look on the image . I tried to replace all 'D' as "Completed" .

SELECT 
    REPLACE(status,'D','COMPLETED')as status from test

but it replaced all the 'D' in the status column.

but my expectation is i want only replace the word ' D'

The result should be like this

- READY 
- FRESH  
- READY  
- COMPLETED  
- COMPLETED  
- COMPLETED

Upvotes: 0

Views: 178

Answers (4)

user3669845
user3669845

Reputation: 31

You need a blank before the D in your "target". If you look at the example below, you can see that the first D (the one in the word AD) is not replaced because the target has a blank before the D and AD has an A before the D. The second D in the string that is being changed does have a blank before it and that D (as well as the blank character in front of it) are changed to "COMPLETED".

You need a target of ' D' not a target of 'D'.

SQL:

SELECT REPLACE('AD D',' D','COMPLETED')

Result:

AD COMPLETED

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

SELECT 
    REPLACE(status,'D','COMPLETED')as status from test
WHERE status='D'

EDIT

     SELECT 
            CASE status when 'D' THEN REPLACE(status,'D','COMPLETED') ELSE status END as status 
     FROM test

Upvotes: 1

DeanOC
DeanOC

Reputation: 7262

If you are trying to get the original value of status unless it is 'D', in which case you want t return 'COMPLETED' instead, then this will do it for you

SELECT CASE WHEN status = 'D' THEN 'COMPLETED' ELSE status END as status from test

Upvotes: 0

Sam
Sam

Reputation: 1366

Instead of REPLACE, you could use CASE to set a different status when the status is 'D':

SELECT CASE WHEN status = 'D' THEN 'COMPLETED'
            ELSE status
       END as status
FROM test

Upvotes: 1

Related Questions