How to parse a string to equal length substrings in SQL

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    Hi

    I need help in a query...How to parse a string to equal length substrings in SQL

    I am getting a long concatenated string from a query (CTVALUE1) and have to use the string in where clause by parsing every 6 characters..

    CREATE TABLE [dbo].[PTEMP](

    [ID] [char](10) NULL,

    [name] [char](10) NULL,

    [CTVALUE1] [char](80) NULL

    )

    INSERT INTO PTEMP

    VALUES('11','ABC','0000010T00010L0001000T010C0001')

    select * from ptemp

    after parsing I have to use these values in a where clause like this

    IN('000001','0T0001','0L0001','000T01','0C0001')

    Now ,the values can change I mean the string may give 5 values(6 character) today and 10 tomorrow..

    So the parsing should be dynamic.

    Any help please

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • Lowell

    SSC Guru

    Points: 323460

    i have this old example, which might help:

    /*--Results

    id testout

    ---------- -------------------- --------------------------------------------------------------------------------

    11 1 000001

    11 2 0T0001

    11 3 0L0001

    11 4 000T01

    11 5 0C0001

    11 6

    */

    CREATE TABLE [dbo].[PTEMP](

    [ID] [char](10) NULL,

    [name] [char](10) NULL,

    [CTVALUE1] [char](80) NULL

    )

    INSERT INTO PTEMP

    VALUES('11','ABC','0000010T00010L0001000T010C0001')

    DECLARE @SplitLength int = 6

    ;with smallTally AS (

    SELECT row_number() OVER (ORDER BY (SELECT NULL)) N FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as T(N)

    )

    ,testData AS (

    SELECT * FROM (VALUES (1,'This is a long string to be split multiple times hopefully'), (2,'Cant touch me')) AS d(id,test)

    )

    SELECT id

    ,row_number() over (partition by id order by n)

    ,substring(CTVALUE1, ((N -1) * @SplitLength) + 1, @SplitLength) testout

    FROM PTEMP d

    CROSS APPLY (SELECT TOP ((len(CTVALUE1) / @SplitLength) + 1) N FROM smallTally) a

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Luis Cazares

    SSC Guru

    Points: 183633

    This is one possibility:

    WITH E(n) AS(

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

    )

    ,Tally(n) AS(

    SELECT (ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) * 6) - 5 FROM E a--, E b /*Uncomment for strings longer than 97 chars and up to 1536 chars long*/

    )

    select ID, name, SUBSTRING( CTVALUE1, n, 6), n

    from ptemp, Tally

    WHERE n < LEN( CTVALUE1);

    It seems that Lowell was faster than me.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 3 posts - 1 through 3 (of 3 total)

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