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'),('220.127.116.11.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.