Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: Trim spaces in the middle of a string

    hi
    is there any function in mysql that trim spaces in the middle of a string?
    for examle

    SELECT FUNCTION_NAME("my sql")
    Return "mysql"

  2. #2
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    From the mysql manual, string functions -

    REPLACE(str,from_str,to_str)

    Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

  3. #3
    Join Date
    Jun 2008
    Posts
    39
    Any way to do this as part of a data cleansing routine?

    One customer has supplied text that looks like this:
    "__100 UF____________________16V_____________6555________ _____ELEC____________SM____"
    Another row:
    "___0.1UF______________16V____________0603________ _____X7R______________________"

    Obviously, it would be much more desirable to store that as:
    "100 UF 16V 6555 ELEC SM"
    "0.1UF 16V 0603 X7R"

    In Excel, the "trim" function removes all whitespace, converting it into single spaces, I believe.
    In Access, it doesn't work that way... in fact, displaying the raw description and the TRIM(description), I actually can't even see a difference...

    If it helps, I will be running this SQL as part of an VBA script that performs the data import. I'm pulling the data from Excel spreadsheets that have been pre-formatted.

    EDIT: Pretend the underscore characters above are spaces!
    In a frustratingly ironic twist of fate, THIS FORUM won't even let me post repeated spaces without truncating them down to one or two!
    Funny.
    Last edited by geolemon; 12-02-08 at 11:48.

  4. #4
    Join Date
    Jun 2008
    Posts
    39
    Please, please please tell me something like THIS isn't the solution:

    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RE PLACE(DBimport.description,"----", "-"),"---","-"),"--","-"),"--","-"),"--","-"),"--","-"),"--","-")

    I might have gone a little overboard - but that's part of what I'm trying to exemplify- why it doesn't seem like the right fix.

    And, although even this might work 99% of the time, it's pretty Rube-Goldberg and has a finite limitation (although, 384 if I did my math right, for this particular combo)...

    That's NOT elegant - there must be a "right" answer... Help me if you know it!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you were to run an update query several times to replace, say, 2 or 3 spaces with one space.
    after you have run the query around 3..6 times you should be down to a single space.. its not nice but it shoudl work

    the only other alternatuve I can think of would be to preprocess the file (using say VBA/PERL/PHP/REXX or something similar) to rip out any offending spaces. the preprocessign could take place as part of the batch job that is loading the data
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Sadly MySQL doesn't support regexp when doing a replace - a regexp would make this quite an easy task. You could easily do this in PHP or Unix/Linux if you process the file with either of these.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by geolemon
    I will be running this SQL as part of an VBA script that performs the data import.
    that's your answer -- collapse the spaces in VBA before importing the data into the database
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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