Transact SQL – PARSENAME

Background

SQL Server’s parsename function allows us to tokenize a string.

Objectname

It is particularly useful when reviewing object names.

ParseName

Sample

SQL


set nocount on
go

set XACT_ABORT on
go

declare @tblObject TABLE
(
	  [id] int not null
		identity(1,1)

	, [objectName] nvarchar(512)
)

insert into @tblObject
(
	[objectname]
)
select '[dbo].[person]'

union all

select '[tempdb].[dbo].[person]'

union all

select '[hrdb].[tempdb].[dbo].[person]'

union all

select '[aws].[hrdb].[tempdb].[dbo].[person]'

select
		  tblO.objectName

		, [object]
			 = parsename(tblO.[objectName], 1)

		, [schema]
			= parsename(tblO.[objectName], 2)

		, [database]
			= parsename(tblO.[objectName], 3)

		, [server]
			= parsename(tblO.[objectName], 4)

		, [dataCenter]
			= parsename(tblO.[objectName], 5)

from   @tblObject tblO

Output

parseName.objectName.20181108.1258PM

Explanation

  1. Works well
    • Things are good till we get to a situation with more than 4 tokens
  2. fails
    • Null is returns for all tokens once the string contains more than 4 tokens

STRING_SPLIT

Sample

SQL

set nocount on
go

set XACT_ABORT on
go

declare @tblObject TABLE
(
      [id] int not null
        identity(1,1)

    , [objectName] nvarchar(512)
)

insert into @tblObject
(
    [objectname]
)
select '[dbo].[person]'

union all

select '[tempdb].[dbo].[person]'

union all

select '[hrdb].[tempdb].[dbo].[person]'

union all

select '[aws].[hrdb].[tempdb].[dbo].[person]'

select
          tblO.objectName

        , [row]
            = ROW_NUMBER() OVER
                (

                    partition by
                        tblO.objectName

                    ORDER BY
                            (
                                select
                                    null
                            ) asc

                ) 

        , tblOSS.[value]

from   @tblObject tblO

CROSS APPLY STRING_SPLIT(tblO.objectName, '.')  tblOSS

order by
          tblO.[id] asc
        , [row] asc

Output

spiltString.objectName.20181108.0115PM

Explanation

  1. Works well
  2. Issues
    • String_Split
      • Rendering
        • Results are displayed in vertical order; whereas most people will likely prefer it in Horizontally
      • Functionality
        • The position of each token in the original stream is lost

XQuery

Sample

Scenario 1

Objective

Form XML Node

SQL

 set nocount on
go

set XACT_ABORT on
go

declare @valueSeparator     varchar(80)

declare @xmlRootNodeBegin   varchar(80)
declare @xmlRootNodeEnd     varchar(80)

declare @xmlNodeBegin   varchar(80)
declare @xmlNodeEnd     varchar(80)

set @valueSeparator = '.'

set @xmlRootNodeBegin = '';
set @xmlRootNodeEnd = '';

set @xmlNodeBegin = '';
set @xmlNodeEnd = '';

declare @tblObject TABLE
(
      [id] int not null
        identity(1,1)

    , [objectName] nvarchar(512)
)

insert into @tblObject
(
    [objectname]
)
select '[dbo].[person]'

union all

select '[tempdb].[dbo].[person]'

union all

select '[hrdb].[tempdb].[dbo].[person]'

union all

select '[aws].[hrdb].[tempdb].[dbo].[person]'

; with cteXQuery
as
(
    select
              tblO.[id]

            , tblO.objectName

            , [xmlRow] =
                cast
                (
                    (

                          @xmlRootNodeBegin

                        + replace

                        (
                              objectName
                            , @valueSeparator
                            , @xmlNodeEnd + @xmlNodeBegin
                        )

                        + @xmlRootNodeEnd
                    )
                    as xml
                ) 

    from   @tblObject tblO

)
select *

from   cteXQuery cteXQ

order by

        cteXQ.[id]

Output

objectName.XQuery.01.20181108.0202PM

Scenario 2

Objective

use XQuery to split XML Node

SQL
set nocount on
go

set XACT_ABORT on
go

declare @valueSeparator     varchar(80)

declare @xmlRootNodeBegin   varchar(80)
declare @xmlRootNodeEnd     varchar(80)

declare @xmlNodeBegin   varchar(80)
declare @xmlNodeEnd     varchar(80)

--set @valueSeparator = '\'
set @valueSeparator = '.'

set @xmlRootNodeBegin = '';
set @xmlRootNodeEnd = '';

set @xmlNodeBegin = '';
set @xmlNodeEnd = '';

declare @tblObject TABLE
(
      [id] int not null
        identity(1,1)

    , [objectName] nvarchar(512)
)

insert into @tblObject
(
    [objectname]
)
select '[dbo].[person]'

union all

select '[tempdb].[dbo].[person]'

union all

select '[hrdb].[tempdb].[dbo].[person]'

union all

select '[aws].[hrdb].[tempdb].[dbo].[person]'

; with cteXQuery
as
(
    select
              tblO.[id]

            , tblO.objectName

            , [xmlRow] =
                cast
                (
                    (

                          @xmlRootNodeBegin

                        + replace

                        (
                              objectName
                            , @valueSeparator
                            , @xmlNodeEnd + @xmlNodeBegin
                        )

                        + @xmlRootNodeEnd
                    )
                    as xml
                ) 

    from   @tblObject tblO

)
, cteXQueryNode
as
(

    select
              [id]

            , cteXQ.objectName

            , cteXQ.xmlRow

            , [rowNumber]
                = ROW_NUMBER()
                    OVER
                    (
                        PARTITION BY
                              [objectName]

                        ORDER BY
                              [objectName] 

                    )

            , [xmlNode]
                = n.value('.','varchar(4000)') 

    from   cteXQuery cteXQ

    cross apply [cteXQ].xmlRow.nodes('/Root/Node') m(n)

)
select *

from   cteXQueryNode

order by
          [id]

Output

objectName.XQuery.02.20181108.0209PM

Scenario 2

Objective

use XQuery to split XML Node and also pivot columns

SQL

set nocount on
go

set XACT_ABORT on
go

declare @valueSeparator     varchar(80)

declare @xmlRootNodeBegin   varchar(80)
declare @xmlRootNodeEnd     varchar(80)

declare @xmlNodeBegin   varchar(80)
declare @xmlNodeEnd     varchar(80)

set @valueSeparator = '.'

set @xmlRootNodeBegin = '';
set @xmlRootNodeEnd = '';

set @xmlNodeBegin = '';
set @xmlNodeEnd = '';

declare @tblObject TABLE
(
      [id] int not null
        identity(1,1)

    , [objectName] nvarchar(512)
)

insert into @tblObject
(
    [objectname]
)
select '[dbo].[person]'

union all

select '[tempdb].[dbo].[person]'

union all

select '[hrdb].[tempdb].[dbo].[person]'

union all

select '[aws].[hrdb].[tempdb].[dbo].[person]'

; with cteXQuery
as
(
    select 

              tblO.[id]

            , tblO.objectName

            , [xmlRow] =
                cast
                (
                    (

                          @xmlRootNodeBegin

                        + replace

                        (
                              objectName
                            , @valueSeparator
                            , @xmlNodeEnd + @xmlNodeBegin
                        )

                        + @xmlRootNodeEnd
                    )
                    as xml
                ) 

    from   @tblObject tblO

)
, cteXQueryNode
as
(

    select
              [id]

            , cteXQ.objectName

            --, cteXQ.xmlRow

            , [rowNumber]
                = ROW_NUMBER()
                    OVER
                    (
                        PARTITION BY
                              [objectName]

                        ORDER BY
                              [objectName] 

                    )

            , [xmlNode]
                = n.value('.','varchar(4000)') 

    from   cteXQuery cteXQ

    cross apply [cteXQ].xmlRow.nodes('/Root/Node') m(n)

)
select
          [id]
        , tblP.objectName
        , [element1] = [1]
        , [element2] = [2]
        , [element3] = [3]
        , [element4] = [4]
        , [element5] = [5] 

from   cteXQueryNode

pivot
(
    max
    (
        [xmlNode]
    )

    for [rowNumber]

    in
            (
                 [1]
               , [2]
               , [3]
               , [4]
               , [5]

            )

) tblP

order by
        [id]
Output

objectName.XQuery.03.20181108.0213PM.PNG