Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Unanswered: Newbie Help field manipulation.

    I am not sure how to technically explain this so please bear with me. I am trying to make a change where one of the columns has data that is returned from a query that puts two groups of numbers in it. Here is what the data looks like in the field.
    ie:
    12345 54321
    ie2:
    1234 54321
    ie3:
    1234 4321
    the field name is client_id in table ups
    So how do I get to remove all of the data and blanks left of the second group of numbers?
    This example would be:
    12345 54321
    would be:
    54321
    or
    12345 4321
    would be:
    4321

    I thought of doing a this:
    SELECT LEFT('XXXXX', 5);
    but dont think this is correct first of all can you do a LEFT with wildcard?
    Then I would just do a trim after that to remove the blank space.
    Any suggestions would be greatly appreciated. And if this is right. Why is it not working?
    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what you do is find the space, and take everything to the right of that

    the position of the space is given by LOCATE(' ',client_id)

    then you take SUBSTRING(client_id,LOCATE(' ',client_id)+1)

    voila
    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
  •