Update column help

[peac3]

Hi guys,

I am trying to update the single column which contains many words
and want to update the word '%main%' to become '%optional%'

the table contains thousand of records.

Any idea how to do this?
database : MSSQL 2005

Thanks in advance guys,

 

[markros]

Try

update myTable 
set ColWords = rtrim(ltrim(replace (' ' + ColWords + ' ',' main ',' optional '))) 
where ' ' + ColWords + ' ' LIKE '% main %'

Assuming that words are separated by spaces and you only want to update whole words.

PluralSight Learning Library

 

[peac3]

Hi makros,

Unfortunately, the columns words are not always the same,

ie
main product
main industrial
selected main code

would like become:
optional product
optional industrial
selected optional code

Thanks,

 

[markros]

Did you try my solution? First to verify it, use SELECT instead of UPDATE, e.g.

select myWordColumn, replace(...) from myTable
where ' ' + myWordColumn + ' ' like '% main %'

PluralSight Learning Library