SQL update part of string-CodePudding

Table name: blist

serial_no   city
abcd_086    US
abcd_087    US
abcd_088    US
abcd_089    US
abcd_090    US

Any sql query which can replace abcd_086 ,087, etc to xyz_086,087,etc. Need to replace first 4 char of serial_no column with xyz. Please help!!!

CodePudding user response：

Replace it

update blist
set serial_no = replace(serial_no, 'abcd_', 'xyz_')
where city = 'US'
  and serial_no like 'abcd%'

CodePudding user response：

the result may vary depending on database. But all of them is close approach. I use postgres db.

UPDATE "blist"
SET "serial_no" = CONCAT('xyz',SUBSTRING("serial_no",5));

CodePudding user response：

SQL provides a very helpful string function called REPLACE that allows you to replace all occurrences of a substring in a string with a new substring.

UPDATE students
SET serial_no = REPLACE(serial_no, 'abcd_', 'xyz_');
WHERE city = 'US' and serial_no like 'abcd_%';

SELECT * FROM STUDENT;

Demo

CodePudding user response：

ANSI SQL way to replace first 4 char of serial_no column with xyz:

update blist
set serial_no = 'xyz' || substring(serial_no from 5)