...

View Full Version : How do I remove first/last quotes from values?



mOrloff
04-03-2009, 11:08 PM
trim() ?, replace() ? ... I'm kinda lost.

I have a table which rcvd a batch of new records this week, and each part number is enclosed in double-quotes.

I need to remove those, and replace() seems to be too broad (i don't want to remove any dbl-Qts if other than first or last, and only where dbl-Qts are first and last char), and I'm not finding a good example on how to use trim() for this.

Please help!

~ Mo

Old Pedant
04-03-2009, 11:45 PM
Does this NEED to be done in the SQL query? Or could/should it be done in your server-side language?

I'm guessing that the data you received was in a CSV file, and the import process messed up and didn't remove the quotes before inserting into the DB. So the better solution (for the future) would be to fix the import process.

mOrloff
04-04-2009, 12:08 AM
Well, I suppose it doesn't NEED to be qn SQL query, but that is what I'm most familiar with.
I'm working the MySQL DB via phpMyAdmin.

BTW: you are 100% right on how this happened, and I am fixing the loophole. For right now, though, I also need to fix what is already in the DB.

Any Suggestions?

~ Mo

Old Pedant
04-04-2009, 02:34 AM
Well, off the top of my head, and untested...



UPDATE tablename SET fieldname = SUBSTRING( fieldname, 2, CHAR_LENGTH(fieldname) - 2 )
WHERE fieldname REGEXP '^\\"' AND fieldname REGEXP '\\"$'

Not sure the \\ are needed there.

Though probably you can just use LIKE:


UPDATE tablename SET fieldname = SUBSTRING( fieldname, 2, CHAR_LENGTH(fieldname) - 2 )
WHERE fieldname LIKE '"%"'

Can't see why that wouldn't work.

We use CHAR_LENGTH instead of just LENGTH in case there are any multi-byte characters in the string. If you *know* you don't have any, you can just use LENGTH.

mOrloff
04-06-2009, 11:40 PM
Once again, Old Pendatnt, .. brilliant.

SUBSTR() is definitely the way to go.
Thanks for the help, multiple solutions, and especially the detailed suggestions.

~ Mo



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum