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?
|
