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?