I have a table's column, with values that need update format from 'A-XXXXX' to A-12-XXXXX' where X is an integer.

I'm using an update like this:

update table_name 
set column = 'A-[0-9][0-9][0-9][0-9][0-9]' 
where column = 'A-12-[0-9][0-9][0-9][0-9][0-9]'; 

but it is not working.

Can anyone make a suggestion on how to do this?

I have a table's column, with values that need update format from 'A-XXXXX' to A-12-XXXXX' where X is an integer.

I'm using an update like this:

update table_name 
set column = 'A-[0-9][0-9][0-9][0-9][0-9]' 
where column = 'A-12-[0-9][0-9][0-9][0-9][0-9]'; 

but it is not working.

Can anyone make a suggestion on how to do this?

• sql
• regex

• 1 just replace “A-” with “A-12-”? – BenderBoy Commented Jan 20 at 1:27
• Please tag the database engine yo are using – DarkBee Commented Jan 20 at 6:43

3 Answers 3

This should work. But I haven't tested it yet.

update table_name 
set column = REPLACE(column, 'A-', 'A-12-') 
where column LIKE 'A-%'; 

You could do it like this:

update table_name
set column =  STUFF(column,3, 0, '12-')
where column LIKE 'A-[0-9][0-9][0-9][0-9][0-9]';

In GoogleSQL you could do something like this:

UPDATE table_name
SET column = REPLACE(column, 'A-', 'A-12-')
WHERE REGEXP_CONTAINS(column, r'A-[0-9]{5}')