Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2013
    Posts
    8

    Unanswered: Get Middle Initial from a Name field-SQL Server 2008

    Hello,
    I need to display the middle initial from a name field that contains the last name, comma, and the middle name or initial.

    Example data:

    Jane,Smith Ron
    John,Dow L
    Mary Jane,Dow Welsh

    The result I am looking for is to capture only the first letter of the middle name. In this data example, I would need to display the following on a separate column:

    R
    L
    W

    Any help will be greatly appreciated.

    RB

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Don't have a test environment so this code is completely untested.
    Code:
    SELECT name
         , Reverse(name)
         , CharIndex(' ', Reverse(name))
         , Right(name, CharIndex(' ', Reverse(name)) - 1)
         , Left(Right(name, CharIndex(' ', Reverse(name)) - 1), 1)
    FROM   your_table
    Should point you in the right direction though
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2013
    Posts
    8
    Thank you for your prompt response.

    I ran the query and got the following error message:

    Msg 536, Level 16, State 2, Line 1
    Invalid length parameter passed to the RIGHT function.

    my query:
    SELECT ALIAS
    , Reverse(ALIAS)
    , CharIndex(' ', Reverse(ALIAS))
    , Right(ALIAS, CharIndex(' ', ALIAS)- 1)
    , Left(Right(ALIAS, CharIndex(' ', Reverse(ALIAS)) - 1), 1)
    FROM PATIENT_ALIAS

    Any ideas?

    Thanks

    RB

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Did you really need to post 3 times? Did I take too long to respond?

    Why don't you comment out one of the lines at a time, starting with the last one in the SELECT statement and work backwards. Once you stop getting error messages look at the VALUES that are returned and see if you can understand why they are what they are.

    Then look at what we do on the next line of the select. I wonder what the -1 could do to provide an invalid value to that function.
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2013
    Posts
    8
    I apologize for the duplicate postings. I am fairly new at this and did not see my post show up the first and second time.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    See this post: http://www.dbforums.com/microsoft-sq...ml#post6606889

    Using the attached function, you can simply call:

    select dbo.FormatName([Your Name String], 'm')

    ...to return the middle initial.
    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
  •