Loading ...

SQL: How to split delimited data using PARSENAME | CodeAsp.Net

SQL: How to split delimited data using PARSENAME

 /5
0 (0votes)

In this blog I will show how to split delimited data in SQL using PARSENAME. From MSDN http://msdn.microsoft.com/en-us/library/ms188006.aspx

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

Let's come to sample code: 

First example:

SELECT  PARSENAME('SERVERNAME.DATABASENAME.SCHEMANAME.ENTITYNAME', 1) AS 'Entity Name' ;
SELECT  PARSENAME('SERVERNAME.DATABASENAME.SCHEMANAME.ENTITYNAME', 2) AS 'Schema Name' ;
SELECT  PARSENAME('SERVERNAME.DATABASENAME.SCHEMANAME.ENTITYNAME', 3) AS 'Database Name' ;
SELECT  PARSENAME('SERVERNAME.DATABASENAME.SCHEMANAME.ENTITYNAME', 4) AS 'Server Name' ;

Output:



Above as you saw the PARSENAME splits the string 'SERVERNAME.DATABASENAME.SCHEMANAME.ENTITYNAME' and returns the specified portion depending upon the second argument.

Second example:

DECLARE @IPADDRESS VARCHAR(20)
SET @IPADDRESS = '192.168.1.1'

SELECT  PARSENAME(@IPADDRESS, 1) 
SELECT  PARSENAME(@IPADDRESS, 2) 
SELECT  PARSENAME(@IPADDRESS, 3) 
SELECT  PARSENAME(@IPADDRESS, 4)


Output:

Above I have used the common example of IP address. Depending upon the second argument of PARSENAME the specified portion of the IP Adress is returned. 

Do let me know your feedback, comments.

Comments (no comments yet)

Top Posts