Split Name

DECLARE @FullName        VARCHAR(100)

SET @FullName = 'John Doe'

SELECT SUBSTRING(@FullName, 1,
CHARINDEX(' ', @FullName) - 1) AS [FirstName],

SUBSTRING(@FullName, CHARINDEX(' ', 
@FullName) + 1, LEN(@FullName)) AS [LastName]

Given the full name of "John Doe", the script will generate

the following output:

FirstName    LastName

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

John         Doe

Aside from the SUBSTRING string function, it also

used the CHARINDEX string function.

The CHARINDEX function returns the starting position

of a specified expression in a character string.

The CHARINDEX function was used to look for

the space that separates the first name from

the last name (CHARINDEX(' ', @FullName)).

If the full name only contains the last name, the following

error message will be encountered:

Server: Msg 536, Level 16, State 3, Line 4

Invalid length parameter passed to the
substring function.

To avoid this error message, the script above needs to be

modified to check if there's a space in the full name.

If there's no space in the full name, it is assumed that it

only contains the last name. The following script

overcomes this problem:

DECLARE @FullName        VARCHAR(100)

SET @FullName = 'JohnDoe'

SELECT
SUBSTRING(@FullName, 1, NULLIF(CHARINDEX(' ',
@FullName) - 1, -1)) AS [FirstName],

SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1,
 LEN(@FullName)) AS [LastName]

The only difference between this script and the previous one

is the addition of the NULLIF function. The NULLIF function

a null value if the two specified expressions are equivalent.

In the CHARINDEX function, if the space is not found in the full name,

it returns a value of 0. Then subtracting 1 from this gives a value of -1

. Using the NULLIF function, we are changing the length parameter passed

to the SUBSTRING function to NULL if the value returned by the

CHARINDEX minus 1 is -1. In the SUBSTRING function

if the length parameter is NULL, the string returned is NULL as well.

, if there's no space in the full name, the first name

will have a value of NULL, as shown in

the following result:

FirstName    LastName

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

NULL         JohnDoe

Using LEFT and RIGHT Functions

The second method of getting the first name

and last name from a full name is using the LEFT

and RIGHT functions. The LEFT and RIGHT

functions are basically the same as the

SUBSTRING function. The LEFT function

returns the part of a character string starting

at a specified number of characters from the

left while the RIGHT function returns the part

of a character string starting a specified number

of characters from the right.

The script to split the full name string into the

corresponding first name and last name using

the LEFT and RIGHT string functions is as follows:

DECLARE @FullName        VARCHAR(100)

SET @FullName = 'John Doe'

SELECT LEFT(@FullName, CHARINDEX(' ',
@FullName) - 1) AS [FirstName],

RIGHT(@FullName, CHARINDEX(' ', 
REVERSE(@FullName)) - 1) AS [LastName]

In getting the first name, it is basically the same as

the first version using the SUBSTRING function.

The only difference is that the LEFT function

doesn't need the starting position and it

automatically starts at the first position.

In getting the last name, the RIGHT string

function is used. The second parameter

of the RIGHT function is the number of

to extract from the full name starting from

the right. To get the number of characters to

extract, the CHARINDEX is again used.

The only difference between the CHARINDEX

used in the LEFT string function and RIGHT

string function is the addition of the use of the

REVERSE string function. Since we want to

extract all characters starting from the space

until the end of the string, we have to

REVERSE the string and count the number of characters

from there (CHARINDEX(' ', REVERSE(@FullName)).

To avoid the same error message discussed

on the first version when there's no space in

the full name, the following script can be used:

DECLARE @FullName        VARCHAR(100)

SET @FullName = 'JohnDoe'

SELECT
LEFT(@FullName, NULLIF(CHARINDEX(' ',
@FullName) - 1, -1)) AS [FirstName],

RIGHT(@FullName, ISNULL(NULLIF(CHARINDEX(' ',
REVERSE(@FullName)) - 1, -1),

LEN(@FullName))) AS [LastName]

It uses the same logic used in the first version which is the

use of the NULLIF function to check if there's a space in the

full name and return a NULL value for the first name

if there's no space. As for the last name, it uses the

NULLIF and ISNULL functions to return the full name

as the last name if there's no space in it. This is done

by passing the length of the full name as the second

parameter to the RIGHT string function if there's no

space in the full name.

Using PARSENAME Function

The last method in splitting a full name into its

first name and last name is the use of the PARSENAME

string function, as can be seen from the following script:

DECLARE @FullName        VARCHAR(100)

SET @FullName = 'John Doe'

SELECT
PARSENAME(REPLACE(@FullName, ' ', '.'), 2)
AS [FirstName],

PARSENAME(REPLACE(@FullName, ' ', '.'), 1)
AS [LastName]

The PARSENAME string function returns the

specified part of an object name. Parts of an

object that can be retrieved are the object name,

owner name, database name, and server name.

In using the PARSENAME string function,

we are tricking SQL Server into thinking

that the value being passed to the first parameter,

which is the object name, is an object within

SQL Server since the function does not validate

whether or not an object by the specified name exists.

The name of an object in SQL Server is composed

the following parts, separated by a period (.):

[Server Name].[Database Name].[Owner Name].[Object Name]

The second parameter of the PARSENAME

function relates to each part of the object name,

each of which having the following values:

• Object Name - 1
• Owner Name - 2
• Database Name - 3
• Server Name - 4

In using the PARSENAME function to

split the full name, we need to replace the space

with a period

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

Then to get the first name, we pass a value of 2 to the

PARSENAME string function, which corresponds

to the [Owner Name]. To get the last name, we pass

a value of 1 to the function, which corresponds to

the [Object Name]. Even if there's no space

separating the first name and last name,

no error message will be encountered and a NULL

value will be returned for the first name while

the whole full name will be returned as the last name.