How to get a substring in SQL Server?

Question

I have the following table, how can I get the substring before and after the dot (.) Of a special character?

MyTable ------------------------------ Id Description ------------------------------ 1 [Hugo].[date].[Subtotal] 2 [Juan].[date].[Subtotal] 3 [7/23/2013].[SubTotal] 4 [7/25/2013].[Total] 

I am looking for the following result

  MyResultTable ------------------------ MyTableId Description depth ----------------------- 1 [Hugo] 1 1 [date] 2 1 [Subtotal] 3 2 [Juan] 1 2 [date] 2 2 [Subtotal] 3 3 [7/23/2013] 1 3 [SubTotal] 2 4 [7/25/2013] 1 4 [Total] 2 

I want to separate the words after the period (.) And list the words as the following table. How can I solve it?

Answer 1

You want to split data based . . You can use recursive CTE to split data and return depth:

 ;with cte (id, DescriptionItem, Description, depth) as ( select id, cast(left(Description, charindex('.',Description+'.')-1) as varchar(50)) DescriptionItem, stuff(Description, 1, charindex('.',Description+'.'), '') Description, 1 as depth from MyTable union all select id, cast(left(Description, charindex('.',Description+'.')-1) as varchar(50)) DescriptionItem, stuff(Description, 1, charindex('.',Description+'.'), '') Description, depth+1 from cte where Description > '' ) select id, DescriptionItem, depth from cte order by id, depth; 

See SQL Fiddle with Demo

Or you can use the UDF function, which splits the data:

 create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1)) returns @temptable TABLE (items varchar(MAX), depth int) as begin declare @idx int declare @slice varchar(8000) declare @depth int = 1 select @idx = 1 if len(@String)<1 or @String is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@String) if @idx!=0 set @slice = left(@String,@idx - 1) else set @slice = @String if(len(@slice)>0) insert into @temptable(Items, depth) values(@slice, @depth) set @String = right(@String,len(@String) - @idx) set @depth = @depth +1 if len(@String) = 0 break end return end; 

Then, when you call the function, you will use CROSS APPLY similar to this:

 select t.id, c.items description, c.depth from mytable t cross apply dbo.split(t.description, '.') c order by t.id, c.depth; 

See SQL Fiddle with Demo

Answer 2

  USE tempdb; GO IF OBJECT_ID('dbo.csv_split','U') IS NOT NULL DROP TABLE dbo.csv_split; CREATE TABLE dbo.csv_split ( Id INT NOT NULL PRIMARY KEY ,Description VARCHAR(100) ) INSERT INTO dbo.csv_split(Id,Description) VALUES (1,'[Hugo].[date].[Subtotal]') ,(2,'[Juan].[date].[Subtotal]') ,(3,'[7/23/2013].[SubTotal]') ,(4,'[7/25/2013].[Total]'); WITH cte_xml AS ( Select csv.Id ,CONVERT(XML,'<desc>' + REPLACE(csv.Description,'.','</desc><desc>') + '</desc>') AS xml_desc From dbo.csv_split csv ) ,cte_shred_xml AS ( Select t.Id ,xml_desc_nodes.value('(.)','varchar(50)') AS Description ,ROW_NUMBER() OVER(PARTITION BY t.Id ORDER BY t.Id ) AS Depth From cte_xml t CROSS APPLY t.xml_desc.nodes('/desc') AS t2(xml_desc_nodes) ) Select * From cte_shred_xml 

Answer 3

Here is a simple example. I created your table as @test and used the cursor and loop inside the cursor.

 DECLARE @test TABLE ( id INT, NAME VARCHAR(MAX) ) INSERT @test VALUES ( 1, '[Hugo].[date].[Subtotal]' ) INSERT @test VALUES ( 2, '[Juan].[date].[Subtotal]' ) INSERT @test VALUES ( 3, '[7/23/2013].[SubTotal]' ) INSERT @test VALUES ( 4, '[7/25/2013].[Total]' ) DECLARE @id INT , @name VARCHAR(MAX) DECLARE @values TABLE ( MyTableId INT , Description VARCHAR(MAX) , Depth INT ) DECLARE @v VARCHAR(2000) , @i INT , @depth INT DECLARE @MyTableList CURSOR SET @MyTableList = CURSOR FOR SELECT id, name FROM @test OPEN @MyTableList FETCH NEXT FROM @MyTableList INTO @id, @name WHILE @@FETCH_STATUS = 0 BEGIN SET @depth = 1 SET @i = PATINDEX('%.%', @name) WHILE @i > 0 BEGIN INSERT @values VALUES ( @id, SUBSTRING(@name, 1, @i - 1), @depth ) SET @name = SUBSTRING(@name, @i + 1, LEN(@name) - @i) SET @i = PATINDEX('%.%', @name) SET @depth = @depth + 1 END INSERT @values VALUES ( @id, @name, @depth ) FETCH NEXT FROM @MyTableList INTO @id, @name END SELECT MyTableId , Description , Depth FROM @values 

You should look like this.

 MyTableId Description Depth 1 [Hugo] 1 1 [date] 2 1 [Subtotal] 3 2 [Juan] 1 2 [date] 2 2 [Subtotal] 3 3 [7/23/2013] 1 3 [SubTotal] 2 4 [7/25/2013] 1 4 [Total] 2