How to get the middle initial for table column First Name John A. and John A

Posted on 2012-08-14
Last Modified: 2012-09-13
Good Day:

I am using SQL Server 2008 R2. I have a view and I need to get the middle initial.  The First Name column appears as George B or George B. with a period at the end.

 Any suggestions how to write SQL to accommodate both cases?  

Thank You,
Question by:DeniseGoodheart
    LVL 9

    Expert Comment

    How about using the first letter after the first space?

    select	upper(substring(@string,charindex(' ',@string)+1,1))

    Open in new window

    LVL 92

    Accepted Solution

    The following works for me.  It tests to see whether the entry ends with "<space><letter><period>" or "<space><letter>".

    CREATE TABLE #tmp (FirstName varchar(50))
    INSERT INTO #tmp (FirstName)
    VALUES ('George B.'), ('George B'), ('George'), ('George Brian')
    SELECT FirstName, 
        CASE WHEN PATINDEX('% [A-Z].', FirstName) > 0 THEN LEFT(RIGHT(FirstName, 2), 1)
            WHEN PATINDEX('% [A-Z]', FirstName) > 0 THEN RIGHT(FirstName, 1)
            ELSE '' END AS MiddleInit
    FROM #tmp
    DROP TABLE #tmp

    Open in new window


    George B.       --->   B
    George B        --->   B
    George           ---> <blank>
    George Brian ---> <blank>
    LVL 68

    Assisted Solution

    This might perform slightly better, particularly on a very large number of rows:

        CASE WHEN RIGHT(FirstName, 2) LIKE ' [A-Z]' THEN RIGHT(FirstName, 1)
                WHEN RIGHT(FirstName, 3) LIKE ' [A-Z][.]' THEN LEFT(RIGHT(FirstName, 2), 1)
                ELSE '' END AS MiddleInit

    Author Comment

    Good Day:

    Sorry for the delay, but I could not access this site with IE for weeks. I finally used FireFox to access this site.  

    Thank You,

    Author Closing Comment

    matthewspatrick provided an excellent complete answer.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now