SQL UPDATE with regular expressions

There are in the table of strings like '123abc', '123bcd'.
They need samnite part of a string (e.g. '123') to another string (such as '345'), but so that the second part remained in place. And this is one request on many, many rows.
Ie, the regular season 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. sql standard), but in a pinch will go a solution for Postgres.

Thank you
October 10th 19 at 12:43
3 answers
October 10th 19 at 12:45
If you want universal, and to replace only the first three characters, then you can get this:

UPDATE table<br> SET field = CONCAT('456', SUBSTRING(field, 4))<br> WHERE field LIKE '123%';

But again, this is only if you need to replace the substring.
If you need to replace in the middle, too, you can get by using SUBSTRING_INDEX() or LOCATE().
Incidentally, the decision without regexps would be faster than them, and, moreover, is universal. And sledovatelno, it is better to choose, if possible.
Op. Thank you. In my case, perfect for a Bang) just what you need. The second bun, alas, can not be given) - Trenton.Cri commented on October 10th 19 at 12:48
And that's not universal(
In Postgres conciencia otherwise done( - Trenton.Cri commented on October 10th 19 at 12:51
Well, then, get together on one bun =)
And as for the concatenation... write yourself in Postgres the CONCAT function which would RETURN a || b; :) - Magnolia.Haag52 commented on October 10th 19 at 12:54
Come on really there, then adapt the queries under muscul (now under postgiro will do), and others.
I have squeeze maximum performance, so it would be better to spend a little more time for adaptations from native remedies - Trenton.Cri commented on October 10th 19 at 12:57
October 10th 19 at 12:47
So should work everywhere:

UPDATE table SET field = REPLACE(field, '123', '456');

And if you need is the regular season, in Postgres like there is a regexp_replace.
Thank you. As a half-measure solution purely for Postgres quite OK, catch bun) But equivalent in the muscle, it seems, not at all..(( - Trenton.Cri commented on October 10th 19 at 12:50
October 10th 19 at 12:49
UPDATE table
SET field = replace(field, '123', '456')
WHERE field LIKE '123%';
replace all occurrences of 123 in the string, not just at the beginning of the line - Trenton.Cri commented on October 10th 19 at 12:52
that's it, and I'd need to in the beginning - Trenton.Cri commented on October 10th 19 at 12:55

Find more questions by tags Regular expressionsSQL