Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    424
    Thanks
    8
    Thanked 6 Times in 6 Posts

    How do I remove first/last quotes from values?

    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

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    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.

  • #3
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    424
    Thanks
    8
    Thanked 6 Times in 6 Posts
    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

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    Well, off the top of my head, and untested...

    Code:
    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:
    Code:
    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.

  • #5
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    424
    Thanks
    8
    Thanked 6 Times in 6 Posts
    Once again, Old Pendatnt, .. brilliant.

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

    ~ Mo


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •