Tag: Split

PARSENAME in SQL Server: a Powerful function

PARSENAME is one of my favourite functions in SQL Server. But, unfortunately, I could not see many people using this function efficiently.So, I thought of sharing some of its usages here with you.

PARSENAME function can be used to retrive specified part of a string that has four parts separated with DOT(.). This function can be used efficiently to retrive the each part name with four part names like fully qualified objectnames.

--Database four part Object name Usage
create Table Test_ParseName(ObjectName Varchar(100))

Insert into Test_ParseName Values('SER1.DB1.SCH1.TAB1'),('SER2.DB2.SCH2.TAB2')

From Test_ParseName

Drop table Test_ParseName

The above is a typical example used in Books online(BOL).

Here, I would like to introduce some more scenarios where we can efficiently use PARSENAME function.

1. To separate IPAdress Sections and for its validation:
Note: IPAddress validation is not a fool-proof solution, however, used to showcase a typical usage of PARSENAME.

create Table Test_ParseName(IPAddress Varchar(100))

Insert into Test_ParseName Values(''),(''),(''),('10.234.12'),('10.234.12.')

Select IPAddress,
		/*Another Usage If we want to check the test is satisfying the 4 part naming separated by "."
		Please note that it does not cover the entire validation of IPAdress*/
		Case when (PARSENAME(IPAddress,1) is not null and PARSENAME(IPAddress ,4) is not null) 
				Then 'Satisfied four part' Else 'Invalid format' End
From Test_ParseName

Drop table Test_ParseName


2. Another example with REPLACE function:
PARSENAME can be used along with REPLACE in many places. Try out the below;the below sample will replace “-” character with “.” and use PARSENAME to separate the four part string easily.

create Table Test_ParseName(string Varchar(100))

Insert into Test_ParseName Values('Check-function-usage-efficiently'),('Check-function-usage'),('Check-function'),('Check'),('Check-function-usage-efficiently-failed')

;With cte_Test_parsename as
(Select REPLACE(string,'-','.') stringmodified From Test_Parsename)

Select stringmodified,
		PARSENAME(stringmodified,4) 'SECTION1',PARSENAME(stringmodified,3) 'SECTION2',
		PARSENAME(stringmodified,2) 'SECTION3',PARSENAME(stringmodified,1) 'SECTION4'
From cte_Test_parsename

Drop table Test_ParseName


Hope you enjoyed this post. I would like to request you to share any other scenario you come across where PARSENAME can be efficiently used.