Understanding the PARSENAME Function in SQL Server

The SQL PARSENAME function is used to extract certain parts of an object name, such as a table name, column name, or database name, based on a specified delimiter. This is especially useful when you’re dealing with objects that have multiple parts separated by a delimiter, such as a dot (“.”), and you need to retrieve a specific part.

This function is like a tool that helps you break down an object name (like a table or column name) into its individual parts. It’s like taking a full name and splitting it into first name, middle name, and last name.

Syntax:

PARSENAME ('object_name' , object_piece )        

Where object_name is the (optionally qualified) name of the object and object_piece is the part you want to return. The object_piece argument must be an int between 1 and 4. The value specifies which part of the object name is returned.

Imagine you have an object name called Server.Schema.Database.Table The PARSENAME function allows you to extract specific parts of that object name based on a given position.

SELECT 
  PARSENAME('PortfolioDB.dbo.alexhouse.alexData', 4) AS [Server],
  PARSENAME('PortfolioDB.dbo.alexhouse.alexdata', 3) AS [Schema],
  PARSENAME('PortfolioDB.dbo.alexhouse.alexdata', 2) AS [Database],
  PARSENAME('PortfolioDB.dbo.alexhouse.alexdata', 1) AS [Table];        

Result:

PARSENAME returns NULL if the object_name or object_piece is NULL.

SELECT 
     PARSENAME('PortfolioDB.dbo.alexhouse', 4) AS [Table];        

Result:

let’s say you have a column called PropertyAddress in a table that stores the full Address of Population. If you want to extract only address before comma(“,”) from each full PropertyAddress, you can use SQL PARSENAME function.

select parsename(replace(PropertyAddress,',','.'),2) Address1,
       parsename(replace(PropertyAddress,',','.'),1) Address2
 FROM [PortfolioDB].[dbo].[alexhouse];        

We are replacing “,” with “.” because “.” is the default delimiter used by the PARSENAME function. The function expects the object name to be in the format of a hierarchical string with parts separated by “.”.

The PARSENAME function splits the object name into parts based on the “.” delimiter. It considers the rightmost part as part 1, the second rightmost part as part 2, and so on. You can then specify the part number you want to extract from the object name, And result will be…

I used SUBSTRING in my previous article to solve the same example and it explains the use of SUBSTRING and CHARINDEX functions.

I hope this example provides you with a practical understanding of how PARSENAME function in real-world situations.