update first 3 chars

[ddtpmyra]

I have records like:

abc -books
acc -papers

now what I wanted to to is to update the first three (3) characters into:

xxx -books
xxx -papers

without doing anything after the dash (-) or leave the 'book' and 'paper'
I tried to do this way but I got error message:

MYSQL

update table1 set LEFT(name,3)='x xx-';

ERROR:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT(name,3)='xxx-' where name in ('books','pens')'' at line 1

[Atli]

Hey.

Try something like:
[code=sql]UPDATE `stuff`
SET `field` = CONCAT('xxx', SUBSTRING(`fiel d`, 4));[/code]

[nbiswas]

Solution to update first 3 chars

Try this

Query1:

update table1 set data = 'xxx' + SUBSTRING(data,4,LEN(data));

Query2:

update table1  set data = 'xxx' + RIGHT(data,LEN(data) - 3)

Output:

data
xxx -books
xxx -papers

[Atli]

That's a bit dangerous though, nbiswas.
The REAPLCE function replaces all occurrences of the input string, so if the first three letters are repeated anywhere else in the string, those would get replaced as well.

[ddtpmyra]

thanks for your help Atli!