Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Splitting space delimited string inline

    So we have a field called forenames, and it needs to be split into fields forename_1, forename_2, forename_3, forename_4 (don't ask).

    Ok, I've come up with this so far, which works, but is pretty nacky in my opinion. Has any one got a better way of achieving this?
    Code:
    SELECT forenames
         , Replace(forenames, ' ', '.')
         , Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 1)) As [f1]
         , Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 2)) As [f2]
         , Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 3)) As [f3]
         , Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 4)) As [f4]
    FROM  (      SELECT 'John' As [forenames]
           UNION SELECT 'John Paul'
           UNION SELECT 'John Paul George'
           UNION SELECT 'John Paul George Ringo'
          ) As [x]
    Results
    Code:
    forenames               (no column name)        f1      f2    f3      f4
    ----------------------  ----------------------    ----  ----  ------  -----
    John                    John                    John	  NULL	NULL    NULL
    John Paul               John.Paul               John	  Paul	NULL    NULL
    John Paul George        John.Paul.George        John	  Paul	George	NULL
    John Paul George Ringo  John.Paul.George.Ringo  John	  Paul	George	Ringo
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So long as you never need more than four it looks just fine to me. Is this a one off or a report?

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's pretty much a 1 off - just wondered if there's a better method out there.
    Never more than4 (in fact, there's only 2 people with that!)
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You guys really ought to read DBForums now and then!

    -PatP

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan
    You guys really ought to read DBForums now and then!

    -PatP
    And you need to read the question

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    George was looking for a better way. I provided a way that seems much better to me. What's the problem?

    -PatP

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    reading dbforums is better than solving george's problem?

    i wholeheartedly agree

    it's a laff riot

    i could do it all day
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's the "inline" part of the question. He doesn't want one row per forename but one column per forename. You could split and then pivot but it is more complex logic, more code and I doubt we'd need to spend much time examining execution plans to see it is less efficient.

    There are a few solutions. But for an inline, easy to understand (once you know what parsename does) and pretty efficient (in terms of writing the code and execution by the engine) I think George's solution is excellent. Yes it is hacky and yes it is a good job no one has more than four forenames but I don't think it can be bettered by much, if at all. By better I mean by the above criteria rather than (for example) portability or other measures.

Posting Permissions

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