How to parse a string to equal length substrings in SQL

Question

Post reply

How to parse a string to equal length substrings in SQL

sql_learner29

SSCrazy Eights

Points: 9107
More actions
January 28, 2015 at 12:51 pm

#294279

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]

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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: 183706 More actions · Answer 2

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

)

,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