update to remove characters from existing data in mysql record

[showman13]

Good afternoon,

I'm drawing a complete blank on this, and decided someone in here could answer the question for me without an issue.

I have a mysql DB with thousands of records in it, and over time have used an inadequate system for maintaining terminated records.

I've been simply prepending ZZZQQQ to the front of the value in the field.

Well, I need to fix that, but when I do, I want to go back and update all those records with that ZZZQQQ in them.

I would like to do a query that updates the field with the current value of the field, minus that ZZZQQQ, and I know there is a way to do it, but just am drawing a blank.

Any suggestions?

It would be something like update table set user = (current user without the ZZZQQQ) where user like ZZZQQQ* or something like that...

Thanks in advance for any feedback.

Douglas

[Reverend Jim]

You can do

UPDATE MyTable
   SET user = REPLACE(user,'ZZZQQQ','')

This will remove all ZZZQQQ strings no matter where they appear in the field.

[BitBlt]

Look at the "Substring" function in the documentation. Use a "where" clause that specifies to return all rows where the first 6 positions of user = 'ZZZQQQ' and then update user = substring(user from 7).

[showman13]

Yep Reverand Jim, that was a quick and easy solution.

thank you

Great base for me to build the whole query on... just exactly what I wanted.

Douglas