SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist ldivinag's Avatar
    Join Date
    Jan 2005
    Location
    N37 33* W122 3*
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    TRIM out spaces in the middle of a string????

    i found a really cool computer inventory program that queries the machine.

    unfortunately, some text that is read, contains spaces like 3-5 in a row.

    for example:

    "my computer 3" sometimes shows up as "my____computer__3" (underscore counts as space since this BB software trims it...) and the TRIM functions only work at the beg and end.

    is regex my only (best???) choice?

    using version 5.0.x.


    thanks.
    leo d.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    use the replace() function, like so: REPLACE(columnname,'__','_')

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2007
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you use REPLACE(columnname,'__','_') once, it will replace 2 spaces with one.
    To replace 3 - 4 spaces with a single space, you will have to run it twice.
    To replace 5 - 8 spaces with a single space, you will have to run it thrice.

    Or you can use preg_replace()

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you do not have to run a REPLACE multiple times, if you choose your strings carefully

    i know i've seen this done with only two nested REPLACEs, but here's one with three that works --
    Code:
    REPLACE(REPLACE(REPLACE(col,'_','_!'),'!_',''),'!','')
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've never seen anything like that Rudy, 'tis a very interesting function combination. What happens if the column contains an exclamation point initially? Would it be better served as:
    Code:
    REPLACE(REPLACE(REPLACE(col,'_','_!'),'!_',''),'_!','_')
    (note the change to the final REPLACE)

    *untested*
    MySQL v5.1.58
    PHP v5.3.6

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, choose a character that isn't represented, e.g. ~ or ^ or summat
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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