Results 1 to 7 of 7

Thread: Substring

  1. #1
    Join Date
    May 2007
    Posts
    3

    Unanswered: Substring

    I want to split "name" column into 3 columns......"lastname" "firstname" "mi" in SQL. Following is the name format....

    Abbott,Gregory A.
    Abel,Guy C.
    Adams,Daniel Lee
    Adams,Doris Jean
    Adams,Kevin B.
    Adams,Lewis Kevin
    Adamson Jr.,Gerald G.
    Aderhold,R. Chuck
    Alden,Bradley Douglas
    Aldrich,Daniel J.

    Can anybody help me....

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Split on comma, then split on space...

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    for a more specific solution, you will want to let us know which database system you're using -- postgresql, sybase, db2, whatever -- so that we can move this thread to the appropriate forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Code:
    SELECT
      SUBSTR(name,1,POSITION(',' IN name)-1) as lastname
     ,SUBSTR(
        SUBSTR(name,
          POSITION(',' IN name)+1,
          LENGTH(name)),
        1,POSITION(' ' IN
            SUBSTR(name,
            POSITION(',' IN name)+1,
            LENGTH(name))
          )-1
      ) AS firstname
     ,SUBSTR(
        SUBSTR(name,
          POSITION(',' IN name)+1,
          LENGTH(name)),
        POSITION(' ' IN
          SUBSTR(name,
          POSITION(',' IN name)+1,
          LENGTH(name))
        ),LENGTH(name)-
          POSITION(' ' IN name)+1
      ) AS middlename

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is what you want to use:
    http://sqlblindman.googlepages.com/formatname
    To get the Last Name, for instance, you would call: dbo.FormatName([YourNameString], 'L').
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    assuming, mister blindman sir, that the original poster is running some version of microsoft sql server

    that's a fair guess, given that so many people cannot tell the difference between microsft sql server as a dbms product and sql as a language

    but this is the sql forum, and not the microsoft sql server forum, so you gots to take that into consideration

    charindex, indeed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I have a version for Access as well, and a buddy of mine converted the logic to .NET.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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