SQL UPDATE with regular expressions

Question

SQL

There are lines in the table like '123abc', '123bcd'.
In them, you need to replace part of the string (for example, '123') with another string (for example, '345'), but in such a way that the second part remains in place. And this is one request for many, many lines.
Those. the regular expression would look like this:

var input = '123abc';
var replaceText = '345$1';
var RegExp = new RegExp(/123(.*)/);
var Result = input.replace(RegExp, replaceText);

and how to do it in SQL updates?
I would like a solution that works in all databases (i.e. on standard sql), but solutions for Postgres will go to the extreme.

Thanks

Answer 1

If you need it universally, and in order to replace only the first three characters, then you can get out like this: But again, this is only if you need to replace an exactly known substring. If you need to replace it in the middle, you can also get out using SUBSTRING_INDEX () or LOCATE (). By the way, a solution without regular expressions will be faster than with them, and, moreover, more versatile. And therefore, it is better to prefer it, if possible.
UPDATE table
SET field = CONCAT('456', SUBSTRING(field, 4))
WHERE field LIKE '123%';

Answer 2

This is how it is difficult to work everywhere:
UPDATE table SET field = REPLACE(field, '123', '456');
And if regular expressions are needed, then Postgres seems to have regexp_replace .

Answer 3

UPDATE table
SET field = replace(field, '123', '456')
WHERE field LIKE '123%';