DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to parse a full name into first, middle and last names?

Execute the following Microsoft T-SQL example scripts in SQL Server Management Studio Query Editor to create and test a table-valued function for parsing a full name into its components.

The following UDF is unrelated to the PARSENAME system function (see below) .
 
------------
-- Microsoft SQL Server T-SQL parse full name into first, middle and last names
------------  
USE NorthWind;

GO

-- SQL Server T-SQL create table-valued function - TVF

CREATE FUNCTION fnParseName

               (@FullName NVARCHAR(128))

RETURNS @FullNameParts TABLE  (FirstName  NVARCHAR(128),

                               Middle     NVARCHAR(128),

                               LastName   NVARCHAR(128))

AS

  BEGIN

    DECLARE  @FirstName  NVARCHAR(128),

             @Middle     NVARCHAR(128),

             @LastName   NVARCHAR(128),

             @word       NVARCHAR(128)

    SET @FullName = RTRIM(LTRIM(REPLACE(@FullName,'.','. ')))

    -- replace double space with a single space - eliminate extra spaces

    -- make sure the following line copies the single and double spaces correcly

    WHILE CHARINDEX('  ',@FullName) > 0

      SET @FullName = REPLACE(@FullName,'  ',' ')

    WHILE CHARINDEX('.',@FullName) > 0

      SET @FullName = REPLACE(@FullName,'.','')

    SET @word = RTRIM(LEFT(@FullName,CHARINDEX(' ',@FullName)))

    IF RIGHT(@word,1) = ','

      SET @LastName = LEFT(@word,LEN(@word) - 1)

    IF LEN(@LastName) > 0

      SET @FullName = LTRIM(RIGHT(@FullName,LEN(@FullName) - LEN(@word)))

    WHILE CHARINDEX(',',@FullName) > 0

      SET @FullName = REPLACE(@FullName,',','')

    IF @LastName IS NULL

      BEGIN

        SET @LastName=LTRIM(RIGHT(@FullName,CHARINDEX(' ',

                                REVERSE(@FullName)+' ')))

        SET @FullName=RTRIM(LEFT(@FullName,LEN(@FullName) - LEN(@LastName)))

      END

    SET @FirstName = RTRIM(LEFT(@FullName,CHARINDEX(' ',@FullName + ' ')))

    SET @FullName = LTRIM(RIGHT(@FullName,LEN(@FullName) - LEN(@FirstName)))

    SET @Middle = @FullName

    INSERT @FullNameParts

    SELECT @FirstName,

           @Middle,

           @LastName

    RETURN

  END

GO

 

-- T-SQL test user defined table-valued function

SELECT CustomerID, FirstName, Middle, LastName, ContactName

FROM Customers c

CROSS APPLY fnParseName(ContactName)

ORDER BY LastName, FirstName, Middle

/*

CustomerID  FirstName   Middle      LastName    ContactName

....

DRACD       Sven                    Ottlieb     Sven Ottlieb

TORTU       Miguel      Angel       Paolino     Miguel Angel Paolino

WELLI       Paula                   Parente     Paula Parente

SAVEA       Jose                    Pavarotti   Jose Pavarott

....

*/

 
-- MSSQL test user defined function with literal input parameter
SELECT FirstName  = RTRIM(FirstName),
       MiddleName = RTRIM(Middle),
       LastName   = RTRIM(LastName)
FROM   fnParseName('MARYJANE H. LIGETY')
GO
/* Results
 
FirstName   MiddleName  LastName
MARYJANE    H           LIGETY
*/
------------
 
 
-- T-SQL PARSENAME function - unrelated to name parsing above
-- Returns components of 4-part DB object reference
USE AdventureWorks;
SELECT PARSENAME('PRODSVR.AdventureWorks.Sales.SalesOrderHeader', 1) AS ObjectName;
-- SalesOrderHeader
SELECT PARSENAME('PRODSVR.AdventureWorks.Sales.SalesOrderHeader', 2) AS SchemaName;
-- Sales
SELECT PARSENAME('PRODSVR.AdventureWorks.Sales.SalesOrderHeader', 3) AS DatabaseName;
-- AdventureWorks
SELECT PARSENAME('PRODSVR.AdventureWorks.Sales.SalesOrderHeader', 4) AS ServerName;
-- PRODSVR
GO

------------

Related articles:

PARSENAME (Transact-SQL)

How can I parse the first, middle and last name from a full name field in SQL?

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE