TSQL - part of string with '.' char-CodePudding

I have string, which can be:

1.A.1  
1.1.A.1

I need SELECT which return me:

1. as 1stLevel, 1.A. as 2ndLevel
1. as 1stLevel, 1.1. as 2ndLevel, 1.1.A. as 3rdLevel

Now I solve this with substring, but have problem when come to 10, on example:

11.D.12.

CodePudding user response：

You can cast strings as xml and use path. You can use table valued function or substring if you are sure with length of your strings.

Xpath example:

if object_id('tempdb..#tmp_xmldata') is not null drop table #tmp_xmldata
select cast('<a>'   replace(input.string, '.', '</a><a>')   '</a>' as xml) as XmlData
into #tmp_xmldata
from (
   select '1.A.1' as string
   union all
   select '1.1.A.1') as input

select
   *,
   XmlData.value('/a[1]', 'varchar(max)') as [1rdLevel],
   XmlData.value('/a[2]', 'varchar(max)') as [2rdLevel],
   XmlData.value('/a[3]', 'varchar(max)') as [3rdLevel],
   XmlData.value('/a[4]', 'varchar(max)') as [4rdLevel]
from #tmp_xmldata as x

Substring:

select
   string,
   substring(string, 1, 1),
   substring(string, 3, 1),
   substring(string, 5, 1),
   substring(string, 7, 1),
   substring(string, 9, 1)
from (
   select '1.A.1' as string
   union all
   select '1.1.A.1') as input

Or you can abuse parsename function:

select
   string,
   parsename(string, 1),
   parsename(string, 2),
   parsename(string, 3),
   parsename(string, 4)
from (
   select '1.A.1' as string
   union all
   select '1.1.A.1') as input

I cannot recomend substring, because you can have issue with non-onechar strings. Best solution is parsename function because its so simple. Limit for parsename are 4 levels.