Search code examples
sqlreplacesubstringsql-server-2016

Replace first word in a string within query

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

Solution

  • 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;