Replace first word in a string within query

user_19263450 0
Viewed 29

running SQL sever 2016

say i have values like

Type
Coat 2
Furry Coat 1
Coat 2
Furry Coat 3
Coat 5
Furry Coat 1
Coat 3
Furry Coat 3

i want to replace all the strings that say "Coat" to "Skinny Coat" within my query but i dont want to include the "Furry Coat" values. how do i do this? i tried using the

replace(Type, 'Coat', 'Skinny Coat') as Type

but it changes all strings

thanks

substring sql-server-2016 sql replace
2 Answers

You Exclude 'Furry Coat'

SELECT  REPLACE(Type, 'Coat', 'Skinny Coat') as Type
FROM TABLE
WHERE TYPE NOT IN ('WHAT','YOU','WANT','TO','EXCLUDE);
user_198543980

You could use CASE logic along with LIKE here:

SELECT Type,
       CASE WHEN Type NOT LIKE 'Furry Coat%'
            THEN REPLACE(Type, 'Coat', 'Skinny Coat') ELSE Type END AS NEW_TYPE
FROM yourTable;
user_18632290
Related