0

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

3
Contributors
3
Replies
19
Views
3 Years
Discussion Span
Last Post by showman13
3

You can do

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

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

Edited by Reverend Jim

Votes + Comments
Nice
0

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).

0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.