parse a string every 6 letters

  • 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]

  • AndrewDBA

    Old Hand

    Points: 362

    Here is how I would solve this problem, however there may be an easier solution:

    CREATE FUNCTION [dbo].[SplitByXChars]

    (

    @string nvarchar(max),

    @segmentLength int

    ) RETURNS @t TABLE

    (

    val nvarchar(max)

    )

    AS

    BEGIN

    declare @i int = 1

    declare @stringlength int = len(@string)

    while (@stringlength - (@i-1) > 0)

    begin

    insert into @t(val)

    select substring(@string, @i, @segmentLength)

    set @i = @i + @segmentLength

    end

    RETURN

    END

    Example use:

    select val

    from [SplitByXChars]('0000010T00010L0001000T010C0001',6)

    or

    select 'test'

    where '000001' in (

    select val

    from [SplitByXChars]('0000010T00010L0001000T010C0001',6)

    )

    Note: the "6" in the above examples indicates to split every 6 characters.

  • Luis Cazares

    SSC Guru

    Points: 183633

    No need for a loop and no need to cross post as it only fragments the solutions.

    Further answers in this thread: http://www.sqlservercentral.com/Forums/Topic1655566-392-1.aspx

    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