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_partparameter, 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
Useful Functions
The PARSENAME() function can be useful for parsing small strings. It returns parts 1-4 (working right to left) of a string, with each part delimited by periods.
For example:
PARSENAME('most.valuable.yak',3) returns “most”
PARSENAME('most.valuable.yak',1) returns “yak”
PARSENAME('most.valuable.yak',4) returns NULL
This can be handy when you need to parse an IP address, or very simple CSV strings. Just REPLACE() the commas with periods and you are good to go. Just remember you are working backwards.
This is a system function, designed to parse SQL Server 4-part names, so it only works with periods and strings with 4 parts. But it can be useful!
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