none
CTE Recursive Query

    Question

  • Hello

    I have a sentence

    This dog, which barks all the time, is getting on my nerves - not (it's only a joke).


    I would like to use a recursive CTE to split the sentence at comma and hypen and brackets.

    Output at this stage
    This dog
    which barks all the time
    is getting on my nerves
    not
    it's only a joke

    Then in the same CTE query I would like to extract each word from the output above.

    Your help is highly appreciated.

    thanks
    Wednesday, March 17, 2010 11:26 AM

Answers

  • thanks

    but I need to use a recursive CTE.

    I also need to extract each word from the list that is produced.

    thanks again
    declare @sep char(1), @s varchar(512)
    select    @s = 'This dog, which barks all the time, is getting on my nerves - not (it''s only a joke)'
    SELECT    @s = REPLACE(REPLACE(REPLACE(@s,'-',','),'(',','),')',','),
            @sep = ',';

    WITH Pieces(pn, start, [stop]) AS
    (
          SELECT 1, 1, CHARINDEX(@sep, @s)
          UNION ALL
          SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
          FROM Pieces
          WHERE stop > 0
    )
        SELECT pn,
          SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
        FROM Pieces

    • Marked as answer by NaeemKhan Friday, March 19, 2010 10:52 AM
    Wednesday, March 17, 2010 1:01 PM

All replies

  • Hello

    I have a sentence

    This dog, which barks all the time, is getting on my nerves - not (it's only a joke).


    I would like to use a recursive CTE to split the sentence at comma and hypen and brackets.

    Output at this stage
    This dog
    which barks all the time
    is getting on my nerves
    not
    it's only a joke

    Then in the same CTE query I would like to extract each word from the output above.

    Your help is highly appreciated.

    thanks
    Hi,

    try below code

    set nocount on
    declare @inputdata varchar(max),
            @seperator nvarchar(5)

    declare @seperatedData TABLE
    (
        id int identity(1,1),
        data nvarchar(4000)
    )

    select @inputdata = 'This dog, which barks all the time, is getting on my nerves - not (it''s only a joke)'

    SELECT @inputdata = REPLACE(REPLACE(REPLACE(@inputdata,'-',','),'(',','),')',','),
            @seperator = ','

    DECLARE @Cnt int
    SELECT    @Cnt = 1

    WHILE (Charindex(@seperator,@inputdata)>0)
    BEGIN
        print ltrim(rtrim(Substring(@inputdata,1,Charindex(@seperator,@inputdata)-1)))
        SELECT    @inputdata = Substring(@inputdata,Charindex(@seperator,@inputdata)+1,len(@inputdata))
        SELECT    @Cnt = @Cnt + 1
    END

    Thanks,
    Mayur





    Wednesday, March 17, 2010 11:57 AM
  • alternative solution is given below



    CREATE FUNCTION [dbo].[udf_GetSplitText]
    (
        @inputdata nvarchar(4000),
        @seperator nvarchar(5)

    RETURNS @seperatedData TABLE
    (
        id int identity(1,1),
        data nvarchar(4000)
    )

    AS 
    BEGIN
        DECLARE @Cnt int
        SELECT    @Cnt = 1

        WHILE (Charindex(@seperator,@inputdata)>0)
        BEGIN
            INSERT INTO @seperatedData (data)
            SELECT    data = ltrim(rtrim(Substring(@inputdata,1,Charindex(@seperator,@inputdata)-1)))

            SELECT    @inputdata = Substring(@inputdata,Charindex(@seperator,@inputdata)+1,len(@inputdata))
            SELECT    @Cnt = @Cnt + 1
        END
       
        INSERT INTO @seperatedData (data)
        SELECT data = ltrim(rtrim(@inputdata))

        RETURN
    END

    go
    declare @t varchar(max)
    select @t = 'This dog, which barks all the time, is getting on my nerves - not (it''s only a joke)'

    SELECT @T = REPLACE(REPLACE(REPLACE(@T,'-',','),'(',','),')',',')


    select id,data from  dbo.udf_GetSplitText(@t,',')


    Wednesday, March 17, 2010 12:00 PM
  • thanks

    but I need to use a recursive CTE.

    I also need to extract each word from the list that is produced.

    thanks again
    Wednesday, March 17, 2010 12:30 PM
  • thanks

    but I need to use a recursive CTE.

    I also need to extract each word from the list that is produced.

    thanks again
    declare @sep char(1), @s varchar(512)
    select    @s = 'This dog, which barks all the time, is getting on my nerves - not (it''s only a joke)'
    SELECT    @s = REPLACE(REPLACE(REPLACE(@s,'-',','),'(',','),')',','),
            @sep = ',';

    WITH Pieces(pn, start, [stop]) AS
    (
          SELECT 1, 1, CHARINDEX(@sep, @s)
          UNION ALL
          SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
          FROM Pieces
          WHERE stop > 0
    )
        SELECT pn,
          SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
        FROM Pieces

    • Marked as answer by NaeemKhan Friday, March 19, 2010 10:52 AM
    Wednesday, March 17, 2010 1:01 PM
  • ANOTHER ALTERNATIVE WAY

    declare @a varchar(1000)
    set @a='This dog, which barks all the time, is getting on my nerves - not (it''s only a joke)'
    SELECT    @A = REPLACE(REPLACE(REPLACE(REPLACE( @a,  ',' , '</M><M>'),'-','</M><M>'),'(','</M><M>'),')','</M><M>')

    ;WITH Cte AS
    (
        SELECT
           CAST('<M>' + @A + '</M>' AS XML)  as id
    )
    select
     Split.a.value('.', 'VARCHAR(100)') AS id
    FROM Cte
    CROSS APPLY id.nodes('/M') Split(a)

    Thanks,
    Mayur
    Thursday, March 18, 2010 6:29 AM
  • thanks
    Friday, March 19, 2010 10:53 AM