Oracle SQL – find the character ‘&’ in a string

oracle

I am intending to do an update (Oracle SQL) for any customer records that includes an ampersand in their name (eg: Joe Bloggs & Co.) to replace this ampersand with the actual word 'and' (eg: Joe Bloggs and Co.).

See script below:

UPDATE acuheader
  SET apar_name = REPLACE (apar_name, '&', 'and') 
where client = 'W5' 
  AND apar_id = 'x'

HOWEVER, prior to doing this, I would have liked to run a select statement to identify all the customer records that have this issue.

I understand that the ampersand sign & is recognised by Oracle SQL as a regular expression & so was looking to see if someone could assist with a select statement.

I had initially thought this would work but not 100% certain:

SELECT * 
from acuheader 
where regexp_like (apar_name,'[&]') 
  and client = 'W5'

Best Answer

My choice for a test would be a simple LIKE:

SELECT id
FROM acuheader
WHERE apar_name LIKE '%&%'
  AND client = 'W5' 
  AND apar_id = 'x';
Related Question