Tip of the Day : Example Uses of the PARSENAME Function

Welcome to SQL Server Helper !!!

This site is intended for those who are beginning to use SQL Server as part of their day-to-day activities.  You will find in this site a collection of useful functions, triggers, stored procedures and tips and tricks related to SQL Server.

Should you have any comments or questions regarding this site or if you want to ask SQL Server-related questions, e-mail us here.

We hope we are able to help and we are glad to help!!!

SQL Server Tip of the Day - May 06, 2016

Example Uses of the PARSENAME Function

The PARSENAME function returns the specified part of an object name.  The parts of an object that can be retrieved are the object name, owner name, database name and server name.  The PARSENAME function does not indicate whether an object by the specified name exists.  PARSENAME just returns the specified part of the specified object name.

PARSENAME ( 'object_name' , object_part )

The  object_name parameter is the name of the object for which to retrieve the specified object part.  The object_part parameter, which is of int data type, is the object part to return and can have a value of 1 for the object name, 2 for the schema name, 3 for the database name and 4 for the server name.

DECLARE @ObjectName SYSNAME
SET @ObjectName = 'MyServer.SQLServerHelper.dbo.Customer'

SELECT PARSENAME(@ObjectName, 1) AS [ObjectName],
PARSENAME(@ObjectName, 2) AS [SchemaName],
PARSENAME(@ObjectName, 3) AS [DatabaseName],
PARSENAME(@ObjectName, 4) AS [ServerName]

ObjectName  SchemaName  DatabaseName     ServerName
----------- ----------- ---------------- -----------
Customer    dbo         SQLServerHelper  MyServer

Sort IP Addresses with PARSENAME

One use of the PARSENAME is with sorting IP addresses.  Similar to a fully qualified object name, an IP address is made of 4 parts separated by a period.  Here’s an example on how to sort IP addresses using the PARSENAME function:

DECLARE @IPAddresses TABLE ( [IPAddress] VARCHAR(20))
INSERT INTO @IPAddresses VALUES ('10.0.0.1')
INSERT INTO @IPAddresses VALUES ('255.255.255.255')
INSERT INTO @IPAddresses VALUES ('192.123.545.12')
INSERT INTO @IPAddresses VALUES ('1.2.3.4')

SELECT * FROM @IPAddresses
ORDER BY CAST(PARSENAME([IPAddress], 4) AS INT),
         CAST(PARSENAME([IPAddress], 3) AS INT),
         CAST(PARSENAME([IPAddress], 2) AS INT),
         CAST(PARSENAME([IPAddress], 1) AS INT)

IPAddress
----------------
1.2.3.4
10.0.0.1
192.123.545.12
255.255.255.255

Split Full Name Into First Name and Last Name with PARSENAME

Another use of the PARSENAME function is to split a 2-part full name into first name and last name.

DECLARE @FullName VARCHAR(50)
SET @FullName = 'Donald Duck'
SELECT PARSENAME(REPLACE(@FullName, ' ', '.'), 2) AS [FirstName],
    PARSENAME(REPLACE(@FullName, ' ', '.'), 1) AS [LastName]

FirstName LastName
----------- ----------
Donald Duck

SQL Server 2012

SQL Server 2008

User-Defined Functions

Date Functions

A collection of useful user-defined functions that deal with dates.

String Functions

A collection of useful user-defined functions that deal with strings (varchar/char/nvarchar/nchar).

Tree Functions

A collection of useful user-defined functions that deal with tree or hierarchical design structures.

Table-Valued Functions

A collection of useful table-valued user-defined functions that can be used to join with other tables.

SQL Server Built-in Functions

A reference to all built-in functions available within SQL Server grouped into categories.

Tips and Tricks

A collection of useful SQL Server-related tips and tricks:

SQL Server Error Messages

A list of SQL Server error messages and for certain error messages, discusses ways on how to solve the error or work around them:

Frequently Asked Questions