How to get substring in SQL Server?

Question

I have the next table, how can I get substring before and after dot(.) 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 a dot(.) and list the words as the following table How can I solve it?

There are many solutions to parse comma-separated strings. You could convert to finding '].' — JeffO
– JeffO, Commented Apr 15, 2013 at 1:08
Is it possible that a Description would contain more than 3 elements? — Tim Lehner
– Tim Lehner, Commented Apr 15, 2013 at 1:53

Taryn · Accepted Answer · 2013-04-15 01:16:01Z

You will want to split the data based on the .. You can use a recursive CTE to split the data and return the 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 a UDF function that 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

Brian Boyd · Accepted Answer · 2013-04-15 02:42:52Z

1

     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

answered Apr 15, 2013 at 2:42

Brian Boyd

2823 silver badges15 bronze badges

1 Comment

Brian Boyd Over a year ago

You could also convert your description field to xml and then shred it.

Kalpers · Accepted Answer · 2013-04-15 01:42:23Z

Here is a simple example. I created your table as @test and used both a cursor and a loop within 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 output 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

Collectives™ on Stack Overflow

How to get substring in SQL Server?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related