“SQL Spackle” is a collection of short articles written based on multiple requests for similar code.  These short articles are NOT meant to be complete solutions.  Rather, they are meant to “fill in the cracks”.

--Phil McCracken

Have you ever heard of the PARSENAME function? Have you ever used it? Is this something that you’ve added to your SQL toolbelt? If you’re like most people that I’ve met, then the answer to all of these would be “No”. This article is going to try to change all of these answers to “Yes”.

Assuming that you have never heard of PARSENAME before, let’s start this journey by going to Books Online (BOL) to see what we can find out about it.

PARSENAME - 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.

SYNTAX: PARSENAME ( 'object_name' , object_piece )

PARAMETERS:

'object_name'

    Is the name of the object for which to retrieve the specified object part. object_name is sysname. This parameter is an optionally-qualified object name. If all parts of the object name are qualified, this name can have four parts: the server name, the database name, the owner name, and the object name.

object_piece

    Is the object part to return. object_piece is of type int, and can have these values:

    1 = Object name

    2 = Schema name

    3 = Database name

    4 = Server name

Inside SQL Server, object names can have four parts – an optional server name, an optional database name, an optional schema name and the name of the object itself. If these are all part of a single string, then this function can return any of these parts to you. Since the only required part is the object name itself, that uses the object_piece value of 1, and the other name parts move to the left from there. So if you want to read these in the same order as the string, you need to start from 4 and work to 1. Let’s go look at an example:

SELECT  ServerName   = PARSENAME(dt.fqn,4),
        DatabaseName = PARSENAME(dt.fqn,3),
        SchemaName   = PARSENAME(dt.fqn,2),
        ObjectName   = PARSENAME(dt.fqn,1)
FROM    (SELECT 'server.database.schema.object' AS fqn) dt;

In this example, the string 'server.database.schema.object' represents a 4-part named object, and the PARSENAME function is utilized to extract out the appropriate part of the object name. The results of this are:

ServerName DatabaseName SchemaName ObjectName

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

server     database     schema     object

Simple enough. And completely boring. After all, how often do you have strings of object names where you need to extract parts of them? Never? Perhaps that is why this function is so underused. Well, let’s look at what else we can do with this function.

The first thing to know about this function is that it doesn’t have to have a 4-part named object. Remove any of the parts, or even multiple parts, and those portions return a null.

You may have noticed that the object names are not using the square brackets identifier delimiters around the parts of the object names. So let’s add those in around each of the parts of the object name. Before you run the code, what do you expect to happen – will the brackets be returned, or not?

SELECT  ServerName   = PARSENAME(dt.fqn,4),
        DatabaseName = PARSENAME(dt.fqn,3),
        SchemaName   = PARSENAME(dt.fqn,2),
        ObjectName   = PARSENAME(dt.fqn,1)
FROM    (SELECT '[server].[database].[schema].[object]' AS fqn) dt;

ServerName DatabaseName SchemaName ObjectName

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

server     database     schema     object

Were you surprised to see the brackets removed? Well, the brackets are just used to delimit the name, and the function is returning the name. It makes sense that it would remove the brackets. However, identifiers can also be delimited with a double-quote. Do you think these would be removed also? Let's find out.

SELECT  ServerName   = PARSENAME(dt.fqn,4),
        DatabaseName = PARSENAME(dt.fqn,3),
        SchemaName   = PARSENAME(dt.fqn,2),
        ObjectName   = PARSENAME(dt.fqn,1)
FROM    (SELECT '"server"."database"."schema"."object"' AS fqn) dt;

ServerName DatabaseName SchemaName ObjectName

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

server     database     schema     object

Limitations

At this point, you may have noticed that all this function is doing is taking a string input, removing the object delimiters, and then splitting the string on the periods, and returning the specified element. Furthermore, since the objects in the example provided don’t exist, the function is not indicating that the object in the string actually exists. Perhaps you have deduced that this function may make a good tool for splitting a string? Well… yes and no. Yes, it is a string splitter. However, there are some limitations.

1. This is not the most efficient means to split strings. See Jeff Moden’s article here for better (much better!!!) performing string splitters.
2. You can only use a period as the delimiter to split on.
3. The identifier delimiters are removed.
4. The specified string can only have 4 parts. If you try using PARSENAME to split the string 'extrainfo.server.database.schema.object' you will get a null value returned.
5. Since this function is designed to return database identifiers, the value returned is limited to 128 characters (a sysname data type, which corresponds to nvarchar(128) ). If it goes beyond this, a NULL will be returned instead.

Time to have fun

Within the limitations that this has, what kind of fun / havoc can we do with the PARSENAME function?

Let’s say you have a table that stores the ip address of people visiting your web site, and you want to create a sorted list of these ip addresses. We’ll start off by creating some test data:

IF OBJECT_ID('tempdb..#IPs') IS NOT NULL DROP TABLE #IPs;
CREATE TABLE #IPs (IPAddress VARCHAR(15));
INSERT INTO #IPs
VALUES  ('10.0.0.15'),
        ('100.0.0.15'),
        ('1.1.1.1'),
        ('2.2.2.2'),
        ('0.0.0.0'),
        ('255.255.255.255')

And when we run a query to sort this:

SELECT  IPAddress
FROM    #IPs
ORDER BY IPAddress;

We see that the results are not sorted numerically. The IP Addresses are stored in an alphanumeric string, and SQL is following the rules for sorting alphanumeric values. We need to get these to sort numeric values by analyzing each individual octet of the IP address. Since an IP address is 4 parts, delimited by a period, we can just use the PARSENAME function (don't forget to keep in mind limitation #1 above!):

SELECT  IPAddress
FROM    #IPs
ORDER BY CONVERT(TINYINT, PARSENAME(IPAddress,4)),
         CONVERT(TINYINT, PARSENAME(IPAddress,3)),
         CONVERT(TINYINT, PARSENAME(IPAddress,2)),
         CONVERT(TINYINT, PARSENAME(IPAddress,1));

As a DBA, I frequently write utility scripts to do things. Sometimes, these scripts need to do things differently based upon the version of SQL Server that the script is running on. I can use the SERVERPROPERTY function to return the ProductVersion, and I have the complete version information. All that I need to do is to get the interested  parts by using PARSENAME:

SELECT  PARSENAME(dt.fqn,4),
        PARSENAME(dt.fqn,3),
        PARSENAME(dt.fqn,2),
        PARSENAME(dt.fqn,1)
FROM (SELECT CONVERT(VARCHAR(20), SERVERPROPERTY('ProductVersion'))) dt(fqn);

Since the SERVERPROPERTY returns a sql_variant, it first needs to be converted into something that can be used - a varchar(20) works great. In my scripts, I usually am interested in the major version, so I would use the object_part 4. However, I can also get the minor version or even the build number using this.

And finally – if you have a string that is delimited with something other than a period (such as a phone number in the xxx-yyy-zzzz format). You can use the REPLACE function to replace the delimiter used with a period.

SELECT  PARSENAME(value,3),
        PARSENAME(value,2),
        PARSENAME(value,1)
FROM    (SELECT REPLACE('800-555-1212', '-', '.')) dt(value);

Reference:

PARSENAME (BOL): http://technet.microsoft.com/en-us/library/ms188006.aspx