Mr.Tananki Mr.Tananki - 1 month ago 16
SQL Question

Updating String in PostgreSQL

In a given database table "Animals",a column called 'anim_name" existed as shown in the picture below
enter image description here

to update the anim_name so that there won't be a '-' .

Example:

'TE-MAEWA 04000' should update as 'TE MAEWA 04000'
'TE-MAEWA 04354 CHAROLAIS CROSS' should update as 'TE MAEWA 04354 CHAROLAIS CROSS'


The following update statement is wrong

update animals
set anim_name = 'TE MAEWA%'
where soc_code = 'AUDV'
and anim_name like 'TE-MAEWA%'


because this would update every animal name to 'TE MAEWA%'. what would be the right update statement.

Answer

Use the function replace():

update animals
set anim_name = replace(anim_name, 'TE-MAEWA', 'TE MAEWA')
where soc_code = 'AUDV' and anim_name like 'TE-MAEWA%';
Comments