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
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
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
REPLACE? => SELECT 'COMPLETED' as status from test WHERE status='D'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
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
