In SQL Server, you can use the PARSENAME() function to return part of an object name.

For example, you can use it to return the schema part (or any other part) of a four part name such as server.schema.db.object.

Syntax

The syntax goes like this:

PARSENAME ( 'object_name' , object_piece )

Where object_name is the (optionally-qualified) object name and object_piece is the part that you want returned.

The object_piece argument must be an int between 1 and 4. The value determines which part of the object name to return. These values correspond to the object part as follows:

Value	Object Part
1	Object name
2	Schema name
3	Database name
4	Server name

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT PARSENAME('Homer.dbo.Music.Artists', 4) AS Result;

Result:

+----------+
| Result   |
|----------|
| Homer    |
+----------+

In this case I returned the server name from a four part object name consisting of the server, schema, database, and table name.

Example 2 – Return All Parts

Here’s what it looks like if I return all parts separately.

DECLARE @object_name char(23) = 'Homer.dbo.Music.Artists';
SELECT 
  PARSENAME(@object_name, 4) AS [Server],
  PARSENAME(@object_name, 3) AS [Schema],
  PARSENAME(@object_name, 2) AS [Database],
  PARSENAME(@object_name, 1) AS [Object];

Result:

+----------+----------+------------+----------+
| Server   | Schema   | Database   | Object   |
|----------+----------+------------+----------|
| Homer    | dbo      | Music      | Artists  |
+----------+----------+------------+----------+

Example 3 – SQL Server Doesn’t Check the Name

It’s important to know that PARSENAME() does not indicate whether an object by the specified name exists. It merely returns the specified part from the given object name.

Therefore, you can pass any value to the function, as long as its a valid sysname.

DECLARE @object_name char(28) = 'completely.bogus.object.name';
SELECT 
  PARSENAME(@object_name, 4) AS [Server],
  PARSENAME(@object_name, 3) AS [Schema],
  PARSENAME(@object_name, 2) AS [Database],
  PARSENAME(@object_name, 1) AS [Object];

Result:

+------------+----------+------------+----------+
| Server     | Schema   | Database   | Object   |
|------------+----------+------------+----------|
| completely | bogus    | object     | name     |
+------------+----------+------------+----------+

Example 4 – Three-Part Object Name

Here’s what happens if I pass a three-part name.

DECLARE @object_name char(17) = 'dbo.Music.Artists';
SELECT 
  PARSENAME(@object_name, 4) AS [Server],
  PARSENAME(@object_name, 3) AS [Schema],
  PARSENAME(@object_name, 2) AS [Database],
  PARSENAME(@object_name, 1) AS [Object];

Result:

+----------+----------+------------+----------+
| Server   | Schema   | Database   | Object   |
|----------+----------+------------+----------|
| NULL     | dbo      | Music      | Artists  |
+----------+----------+------------+----------+

Example 5 – Two-Part Object Name

Here’s what happens if I pass a two-part name.

DECLARE @object_name char(13) = 'Music.Artists';
SELECT 
  PARSENAME(@object_name, 4) AS [Server],
  PARSENAME(@object_name, 3) AS [Schema],
  PARSENAME(@object_name, 2) AS [Database],
  PARSENAME(@object_name, 1) AS [Object];

Result:

+----------+----------+------------+----------+
| Server   | Schema   | Database   | Object   |
|----------+----------+------------+----------|
| NULL     | NULL     | Music      | Artists  |
+----------+----------+------------+----------+

Example 6 – One-Part Object Name

And, as silly as it may seem, a one-part name.

DECLARE @object_name char(7) = 'Artists';
SELECT 
  PARSENAME(@object_name, 4) AS [Server],
  PARSENAME(@object_name, 3) AS [Schema],
  PARSENAME(@object_name, 2) AS [Database],
  PARSENAME(@object_name, 1) AS [Object];

Result:

+----------+----------+------------+----------+
| Server   | Schema   | Database   | Object   |
|----------+----------+------------+----------|
| NULL     | NULL     | NULL       | Artists  |
+----------+----------+------------+----------+

Example 7 – Five-Part Object Name

Here’s what happens if you provide an object name with more than four parts.

DECLARE @object_name char(23) = 'Oops.Homer.dbo.Music.Artists';
SELECT 
  PARSENAME(@object_name, 4) AS [Server],
  PARSENAME(@object_name, 3) AS [Schema],
  PARSENAME(@object_name, 2) AS [Database],
  PARSENAME(@object_name, 1) AS [Object];

Result:

+----------+----------+------------+----------+
| Server   | Schema   | Database   | Object   |
|----------+----------+------------+----------|
| NULL     | NULL     | NULL       | NULL     |
+----------+----------+------------+----------+

Example 8 – Other Uses

Given you can provide a completely bogus object name, PARSENAME() can be a handy hack for splitting other delimited data, such as IP4 addresses.

DECLARE @object_name char(15) = '172.217.167.110';
SELECT 
  PARSENAME(@object_name, 4) AS [4],
  PARSENAME(@object_name, 3) AS [3],
  PARSENAME(@object_name, 2) AS [2],
  PARSENAME(@object_name, 1) AS [1];

Result:

+-----+-----+-----+-----+
| 4   | 3   | 2   | 1   |
|-----+-----+-----+-----|
| 172 | 217 | 167 | 110 |
+-----+-----+-----+-----+

However, if you really need to do this, consider using a different method, such as STRING_SPLIT().

Example 9 – Commas and Other Delimiters

Given PARSENAME() is intended to be used with object names, you can’t use commas as delimiters and expect it to work the same.

Here’s what happens if I try to do that.

DECLARE @object_name char(23) = 'Homer,dbo,Music,Artists';
SELECT 
  PARSENAME(@object_name, 4) AS [Server],
  PARSENAME(@object_name, 3) AS [Schema],
  PARSENAME(@object_name, 2) AS [Database],
  PARSENAME(@object_name, 1) AS [Object];

Result:

+----------+----------+------------+-------------------------+
| Server   | Schema   | Database   | Object                  |
|----------+----------+------------+-------------------------|
| NULL     | NULL     | NULL       | Homer,dbo,Music,Artists |
+----------+----------+------------+-------------------------+

You’d need to replace the commas with a dot if you want to do that, or use a different method, such as STRING_SPLIT().