CODEWITHSUNDEEP
mysql
Syed Mudabbir
Syed Mudabbir

Reputation: 287

Replace the text of whole column it contains specific string mysql

I have a question in mysql about searching the column in a table and if it contains specific string replace whole column with particular text.

Lets say If the column containing this textdata/variant_image/2/IW_HH_1B_sxph-2o_l7ar-4r_kh2k-fd.jpg has this string in itIW_HH_1B_ then replace it with data/variant_image/1/IW_HH_1B.jpg

I tried update,replace functions but it didnt work. And searching on google returns only replacing that particular string option. but not complete column text.

Upvotes: 0

Views: 71

Answers (2)

Inanda Menezes
Inanda Menezes

Reputation: 1804

A simple update works: 

UPDATE your_table
    SET your_column = 'data/variant_image/1/IW_HH_1B.jpg'
    WHERE your_column LIKE '%IW_HH_1B_%'

Upvotes: 1

Gervs
Gervs

Reputation: 1397

You can use a conditional update for that:

UPDATE tablename
SET columnname = 'data/variant_image/1/IW_HH_1B.jpg'
WHERE LOCATE('IW_HH_1B' COLLATE utf8_bin, columnname) > 0

If you want it to be case insensitive leaf the collate part out.

Upvotes: 0

Related Questions