PARSENAME in SQL Server: a Powerful function

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

PARSENAME_image2

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

PARSENAME_image3

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.

Advertisements

2 thoughts on “PARSENAME in SQL Server: a Powerful function

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

    Like

    • 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

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s