PARSENAME( ) FUNCTION IN SQL SERVER

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

NOTE :  
         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.


SYNTAX:

          PARSENAME ( 'object_name' , object_part )

'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

return type :  nchar

examples :

DECLARE @ObjectName SYSNAME
SET @ObjectName = 'Server.emaildb.dbo.table '


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
----------- ----------- ---------------- -----------
table         dbo           emaildb        Server

2:

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



3:

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
 

4. 
select parsename('babji.naveen,fff.jana.praveen,__ann',1)

IP Addresses A common question in the SQLTeam forums is something along the lines of "How to I break up IP address data to group by subnet ?" - See more at: http://www.sqlteam.com/article/using-the-parsename-function-to-split-delimited-data#sthash.9yHO1EdL.dpuf

ans: praveen,__ann