CODEWITHSUNDEEP
mysqlsql-update
santanu
santanu

Reputation: 5547

How to prepend a string to a column value in MySQL?

I need a SQL update statement for updating a particular field of all the rows with a string "test" to be added in the front of the existing value.

For example, if the existing value is "try" it should become "testtry".

Upvotes: 147

Views: 180255

Answers (5)

bvidinli
bvidinli

Reputation: 131

Many string update functions in MySQL seems to be working like this: If one argument is null, then concatenation or other functions return null too. So, to update a field with null value, first set it to a non-null value, such as ''

For example:

update table set field='' where field is null;
update table set field=concat(field,' append');

Upvotes: 13

user3419778
user3419778

Reputation: 866

  • UPDATE table_name SET Column1 = CONCAT('newtring', table_name.Column1) where 1
  • UPDATE table_name SET Column1 = CONCAT('newtring', table_name.Column2) where 1
  • UPDATE table_name SET Column1 = CONCAT('newtring', table_name.Column2, 'newtring2') where 1

We can concat same column or also other column of the table.

Upvotes: 1

Paul Dixon
Paul Dixon

Reputation: 300825

You can use the CONCAT function to do that:

UPDATE tbl SET col=CONCAT('test',col);

If you want to get cleverer and only update columns which don't already have test prepended, try

UPDATE tbl SET col=CONCAT('test',col)
WHERE col NOT LIKE 'test%';

Upvotes: 298

soulmerge
soulmerge

Reputation: 75704

That's a simple one

UPDATE YourTable SET YourColumn = CONCAT('prependedString', YourColumn);

Upvotes: 7

Ferdinand Beyer
Ferdinand Beyer

Reputation: 67137

UPDATE tablename SET fieldname = CONCAT("test", fieldname) [WHERE ...]

Upvotes: 18

Related Questions