|
|
One common task performed in database applications is given a full name, how can
this be split into the first name and last name. In this article, three
different methods are discussed to perform this task using three different
functions in SQL Server. It is assumed in this article that the full name
is just made up of one first name and one last name.
Using SUBSTRING Function
The first method is the use of the SUBSTRING string function, as can be seen
from the following script:
|
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 returns 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. Therefore, 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 characters 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 corresponding 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 of 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.
|