Split without delimiter based on position

  • I am having a query i need to split the query based on the position i specify

    Say

    string is 12345678910111213141516........

    i am having position as 1,1,2,5,4,2

    i need as

    1

    2

    34

    56789

    1011

    12

    Thanks

  • USE BELOW CODE...

    --DROP TABLE #temp1

    declare @STR varchar(100)='12345678910111213141516'

    declare @pos varchar(50)='1,1,2,5,4,2'

    declare @t int=0

    create table #temp1(id varchar(100))

    WHILE CHARINDEX(',',@pos)>0

    BEGIN

    set @t=SUBSTRING(@pos,1,(CHARINDEX(',',@pos)-1))

    INSERT INTO #temp1 VALUES( substring(@str,1,@t))

    SET @pos=SUBSTRING(@pos,(CHARINDEX(',',@pos))+1,LEN(@pos))

    set @STR=SUBSTRING(@str,(@t+1),len(@str))

    END

    INSERT INTO #temp1 VALUES (SUBSTRING(@str,1,CONVERT(INT,@pos)))

    --INSERT INTO @T1 VALUES(@VAL)

    SELECT * FROM #temp1

  • hey it has worked thanks

    now the thing is i need to insert this data into a table

    my table is

    create testtable (Rid int identity(1,1),col1 int,col2 int,col3 int,col4 int,col5 int,col6 int)

    i need to insert this data i this table like

    insert into testtable values ........

    and my result from this table will be like something

    Select * from testtable

    Rid,col1,col2,col3,col4,col5,col6

    1 ,1,2,34,56789,1011,12

    Thanks

  • I have used Pivot pls correct me if i have done wrong

    DECLARE @cols NVARCHAR(2000)

    SELECT @cols = COALESCE(@cols + ',[' + Convert(varchar,Rid )+ ']', '[' + Convert(varchar,Rid ) + ']')

    FROM #Temp

    DECLARE @query NVARCHAR(4000)

    SET @query = N'SELECT '+

    @cols +'FROM (SELECT [Rid],id FROM #Temp) p

    PIVOT

    (min([id]) FOR [Rid] IN ( '+@cols +' )) AS pvt'

    print @query

    EXECUTE(@query)

  • Without while loop can we do this?

    since loop is taking more time any other alternate method

    Thanks!

  • Hi

    I am getting the error as

    declare @pi_input varchar(max)

    declare @query varchar(max)

    select @pi_input ='0123456'

    SET @query = N'Select '+'SUBSTRING(@Input,'+convert(varchar,0) +',1)'

    print @query

    exec (@query)

    Select SUBSTRING(@Input,0,1)

    Msg 137, Level 15, State 2, Line 1

    Must declare the scalar variable "@Input".

    how to resolve it

    thanks!

  • yuvipoy (10/19/2012)


    Hi

    I am getting the error as

    declare @pi_input varchar(max)

    declare @query varchar(max)

    select @pi_input ='0123456'

    SET @query = N'Select '+'SUBSTRING(@Input,'+convert(varchar,0) +',1)'

    print @query

    exec (@query)

    Select SUBSTRING(@Input,0,1)

    Msg 137, Level 15, State 2, Line 1

    Must declare the scalar variable "@Input".

    how to resolve it

    thanks!

    Not sure what this has to do with your original question but your dynamic sql is nowhere close here. You have @Input in your dynamic sql. That variable MUST be declared inside the dynamic sql. That variable is not declared outside your dynamic sql either so I am not sure what that is. Honestly from you posted I don't see any reason for dynamic sql at all.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • This looked like an extremely odd situation but a fun challenge. It can in fact be accomplished without loops. You have to combine a couple of techniques, both the delimited split and the quirky update.

    You need to read the article referenced in my signature about splitting strings. In there you will find the code for the DelimitedSplit8K function.

    Then you need to read this article about running totals. http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]

    Why running totals? I use the running total to keep track of the length of the string at the time of each split.

    Make sure that you read and understand the concepts in both articles. These are pretty advanced topics and you are the one who has to support this code.

    declare @SomeString varchar(50) = '12345678910111213141516'

    declare @Pos varchar(50) = '1,1,2,5,4,2'

    --For the quirky update we need this data in a table of some sort.

    create table #StringOutput

    (

    RowNum int,

    ColLength int,

    SumOfLength int

    )

    insert #StringOutput

    select *, null --The null will be updated with the running total

    from dbo.DelimitedSplit8K(@pos, ',')

    declare @Total int = 0

    --this is the quirky update used to store the running total

    update #StringOutput

    set @Total = SumOfLength = @Total + ColLength

    OPTION (MAXDOP 1)

    --now that we know where in the original string and the length of each segment we just need to get the correct substring for each segment.

    select *, substring(@SomeString, SumOfLength - ColLength + 1, ColLength) as FinalOutput

    from #StringOutput

    drop table #StringOutput

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Hi, thanks i have learned one new thing today,

    but the thing is i need to insert into the testtable

    Select * from testtable

    Rid,col1,col2,col3,col4,col5,col6

    1 ,1,2,34,56789,1011,12

    my testtable will be a dynamic one based on the input of the position in my example i have given

    declare @STR varchar(100)='12345678910111213141516'

    declare @pos varchar(50)='1,1,2,5,4,2'

    @pos may increase or decrease if it increase i need to create my testtable accordingly ,here it is 6 splits so i am having 6 columns later i may get 15 columns so i need to create a testtable with 15 columns and so on and i need to insert the data vertically after splitting.

  • yuvipoy (10/18/2012)


    hey it has worked thanks

    now the thing is i need to insert this data into a table

    my table is

    create testtable (Rid int identity(1,1),col1 int,col2 int,col3 int,col4 int,col5 int,col6 int)

    i need to insert this data i this table like

    insert into testtable values ........

    and my result from this table will be like something

    Select * from testtable

    Rid,col1,col2,col3,col4,col5,col6

    1 ,1,2,34,56789,1011,12

    Thanks

    The following query will meet this requirement:

    DECLARE @STR VARCHAR(100) = '12345678910111213141516'

    DECLARE @pos VARCHAR(50) = '1,1,2,5,4,2'

    ;WITH

    -- resolve @pos into a table

    ResolvedString AS (

    SELECT

    ItemNumber,

    Item = CAST(Item AS INT)

    FROM dbo.DelimitedSplit8K(@pos,',')

    )

    -- crosstab query

    SELECT

    IDENTITY (int, 1, 1) AS Rid,

    Col1 = MAX(CASE WHEN r.ItemNumber = 1 THEN y.Word ELSE NULL END),

    Col2 = MAX(CASE WHEN r.ItemNumber = 2 THEN y.Word ELSE NULL END),

    Col3 = MAX(CASE WHEN r.ItemNumber = 3 THEN y.Word ELSE NULL END),

    Col4 = MAX(CASE WHEN r.ItemNumber = 4 THEN y.Word ELSE NULL END),

    Col5 = MAX(CASE WHEN r.ItemNumber = 5 THEN y.Word ELSE NULL END),

    Col6 = MAX(CASE WHEN r.ItemNumber = 6 THEN y.Word ELSE NULL END)

    INTO #Temp

    FROM ResolvedString r

    -- use a triangular join to calculate the start position for SUBSTRING()

    CROSS APPLY (

    SELECT Startpos = 1+ISNULL(SUM(ir.Item),0)

    FROM ResolvedString ir

    WHERE ir.ItemNumber < r.ItemNumber

    ) x

    CROSS APPLY (

    SELECT Word = SUBSTRING(@str,x.Startpos,r.Item)

    ) y

    SELECT * FROM #Temp

    Couple of questions for you:

    Where do the input string and the positions string come from? How are they created?

    What's downstream from the new table?


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • yuvipoy (10/22/2012)


    @pos may increase or decrease if it increase i need to create my testtable accordingly ,here it is 6 splits so i am having 6 columns later i may get 15 columns so i need to create a testtable with 15 columns and so on and i need to insert the data vertically after splitting.

    Can you provide some more information about the whole process?


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • Starting from my first post i will cont.. here

    I am having a query i need to split the query based on the position i specify

    Say

    string is 12345678910111213141516........

    i am having position as 1,1,2,5,4,2

    i need as

    1

    2

    34

    56789

    1011

    12

    string is 12345678910111213141516........ which will be input for me.

    i am having position as 1,1,2,5,4,2 i am having this in a table

    say table will look like

    Splitter table

    Rid , Delimiter

    1,1

    2,1

    3,2

    4,5

    5,4

    6,2

    in the CTE example you have hard coded Col1,Col2..Col6 but this will be more or less for me for some time so i can't hardcoded as Col1..Col6 since for some time i may have splitter table to take only 4 values which means

    Rid , Delimiter

    1,1

    2,1

    3,2

    4,5

    for this input string

    string is 12345678910111213141516........

    rest i can ignore

    Rid , Delimiter

    1,1

    2,1

    for the same input string

    string is 12345678910111213141516........

    rest i can ignore

    Rid , Delimiter

    1,1

    2,1

    3,2

    4,5

    5,4

    6,2

    7,2

    for the same input string

    string is 12345678910111213141516........

    the above statement is what i am saying as dynamic based on the split condition

    Thanks!

  • yuvipoy (10/22/2012)


    Starting from my first post i will cont.. here...

    We know all of this already. What can you tell us about

    1. The process which generates these two strings - where do they come from?

    2. How the data is consumed, once you have created a table with x colums and y rows.

    Thanks


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • We know all of this already. What can you tell us about

    1. The process which generates these two strings - where do they come from?

    2. How the data is consumed, once you have created a table with x colums and y rows.

    Thanks

    1) @string is the input from the code which will be passed to stored procedure

    and table splitter is a default one there will not be any change in the column except we take some certain columns based on the third parameter , the third parameter will tell us what is the column we need to take from splitter table(eg: 6 or 4 or 2 or 7....) third parameter will tell us the splitter details.

    2) the data is pre generated before the start of the process for the splitter table

    Thanks

  • yuvipoy (10/22/2012)


    We know all of this already. What can you tell us about

    1. The process which generates these two strings - where do they come from?

    2. How the data is consumed, once you have created a table with x colums and y rows.

    Thanks

    1) @string is the input from the code which will be passed to stored procedure

    and table splitter is a default one there will not be any change in the column except we take some certain columns based on the third parameter , the third parameter will tell us what is the column we need to take from splitter table(eg: 6 or 4 or 2 or 7....) third parameter will tell us the splitter details.

    2) the data is pre generated before the start of the process for the splitter table

    Thanks

    What generates @string? What generates @pos?


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Viewing 15 posts - 1 through 15 (of 35 total)

You must be logged in to reply to this topic. Login to reply