Have you ever heard of the PARSENAME function in SQL Server? If not, you’re not alone. This function is often overlooked and underused, but it can be a powerful tool in your SQL toolbelt.

The PARSENAME function is used to retrieve specific parts of an object name in SQL Server. These parts can include the object name, owner name, database name, and server name. By specifying the object part and the object name, you can extract the desired information.

Let’s take a 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 PARSENAME function is used to extract the server name, database name, schema name, and object name from the string ‘server.database.schema.object’. The result of this query would be:

ServerName   DatabaseName   SchemaName   ObjectName
----------   ------------   ----------   ----------
server       database       schema       object

While this example may seem simple and straightforward, the PARSENAME function can be used in more complex scenarios. For instance, you can use it to split a string into multiple parts. However, there are some limitations to keep in mind:

• The function can only split a string using a period as the delimiter.
• The identifier delimiters, such as square brackets or double quotes, are removed.
• The specified string can only have a maximum of four parts.
• The function is designed to return database identifiers, so the returned value is limited to 128 characters.

Despite these limitations, the PARSENAME function can still be a useful tool in your SQL Server arsenal. Let’s explore a few more examples:

Sorting IP Addresses:

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));

In this example, the PARSENAME function is used to sort a list of IP addresses numerically. By splitting the IP address into its individual octets and converting them to TINYINT, we can achieve the desired sorting.

Retrieving SQL Server Version Information:

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);

In this example, the PARSENAME function is used in conjunction with the SERVERPROPERTY function to retrieve specific parts of the SQL Server version information. By specifying the object part, you can extract the major version, minor version, or build number.

Using a Different Delimiter:

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

In this example, the PARSENAME function is used to split a string delimited by a hyphen into its individual parts. By replacing the hyphen with a period using the REPLACE function, we can then use the PARSENAME function to extract the desired information.

As you can see, the PARSENAME function can be a handy tool in various scenarios. While it may have some limitations, it can still provide valuable functionality in your SQL Server queries.

For more information on the PARSENAME function, you can refer to the official documentation in the SQL Server Books Online.