Introduction:
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')
Select PARSENAME(ObjectName,4) 'SERVERNAME',PARSENAME(ObjectName,3) 'DBNAME',PARSENAME(ObjectName,2) 'SCHEMANAME',PARSENAME(ObjectName,1) 'OBJNAME'
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.123.123.234'),('10.234.12.123'),('101.123.234.12.123'),('10.234.12'),('10.234.12.')
Select IPAddress,
PARSENAME(IPAddress,4) 'SECTION1',PARSENAME(IPAddress,3) 'SECTION2',
PARSENAME(IPAddress,2) 'SECTION3',PARSENAME(IPAddress,1) 'SECTION4',
/*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.
SQLZealots 
How is this useful? This is only good for strings that have a set number of elements. If the string changes from 4 to 3. It does not pull the right information. It will pull a value for 1 since it is at the end of the string and Null for the 4th position. Not useful when you want to element 1 to be splint 1.
Example.– ‘aEmail1;aEmail2;aEmail3;aEmail4’ (using the replace to change . to , and ; to .
‘aEmail1;aEmail2;aEmail3’
Need this..
Email1 = aEmail1
Email2 = aEmail2
Email3 = aEmail3
Email4 = aEmail4
But gets this.
Email1 = aEmail4
Email2 = aEmail3
Email3 = aEmail2
Email4 = aEmail1
Then the 2nd String needs
Email1 = aEmail1
Email2 = aEmail2
Email3 = aEmail3
Email4 = NULL
Gets
Email1 = aEmail3
Email2 = aEmail2
Email3 = aEmail1
Email4 = NULL
LikeLike
Try the below: (I have not tested all of your req, however, this is something you can really work it out. The blog tries to introduce some of the usages, not all definitely…:))
create Table Test_ParseName(string Varchar(100))
Insert into Test_ParseName Values(‘aEmail1;aEmail2;aEmail3;aEmail4’),(‘aEmail1;aEmail2;aEmail3′)
;With cte_Test_parsename as
(Select REPLACE(string,’;’,’.’) stringmodified From Test_Parsename)
,cteFOR_EDQ as
(Select Case when len(stringmodified) – len(replace(stringmodified,’.’,”)) 3 then stringmodified + replicate(‘.*’, 3-(len(stringmodified) – len(replace(stringmodified,’.’,”)))) else stringmodified end Stringfor_EDQ
From cte_Test_Parsename)
Select
PARSENAME(Stringfor_EDQ,4) ‘SECTION1’,PARSENAME(Stringfor_EDQ,3) ‘SECTION2’,
PARSENAME(Stringfor_EDQ,2) ‘SECTION3’,PARSENAME(Stringfor_EDQ,1) ‘SECTION4’
From cteFOR_EDQ
Drop table Test_ParseName
LikeLike