mysql更新帮助需要为条目添加前缀

Question

Sorry for the poor title, but I couldn't think of another way to describe it. What I need to do is, add a prefix to certain entries in a column.

For example, a column could have number entries such as: 92209.1,92201,1,92202,1 etc. I need to add a prefix to only numbers without the prefix. 92209.1 becomes TMP92201,1.

I know how to use the UPDATE function, but not sure how to use with this type of query. I would be grateful for any help that you can offer. Thanks

Answer 1

You should use a statement along the lines of:

UPDATE TABLE_NAME SET FIELD_NAME = CONCAT('TMP', RIGHT(FIELD_NAME, LENGTH(FIELD_NAME) - 7)) WHERE FIELD_NAME LIKE '92209.1%'

Essentially, it'll change the value of the field named FIELD_NAME from the table called TABLE_NAME for every row that has a FIELD_NAME starting with '92209.1'. The value that will be set is obtained by concatenating 'TMP' and the value of FIELD_NAME to which we removed the left portion of the size of the prefix (7 characters).