Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 878
  • Last Modified:

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

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,
Denise
0
DeniseGoodheart
Asked:
DeniseGoodheart
2 Solutions
 
McOzCommented:
How about using the first letter after the first space?

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

Open in new window

0
 
Patrick MatthewsCommented:
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


Thus:

George B.       --->   B
George B        --->   B
George           ---> <blank>
George Brian ---> <blank>
0
 
Scott PletcherSenior DBACommented:
This might perform slightly better, particularly on a very large number of rows:

SELECT
    FirstName,    
    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
0
 
DeniseGoodheartAuthor Commented:
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,
D
0
 
DeniseGoodheartAuthor Commented:
matthewspatrick provided an excellent complete answer.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now