Updating String in PostgreSQL

Question

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

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.

I would create a CTE that selects the uuid of the row, and the anim_name without the -. Then use that CTE to do an update with a join. Have a look here: stackoverflow.com/a/25358642/4080476 — Brian Pendleton
– Brian Pendleton, Commented Oct 19, 2016 at 23:59

klin · Accepted Answer · 2016-10-20 00:07:20Z

5

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%';

answered Oct 20, 2016 at 0:07

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1