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]

• Lowell

  SSC Guru

  Points: 323527

  More actions

  January 28, 2015 at 1:04 pm

  #1773802

  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

  January 28, 2015 at 1:10 pm

  #1773803

  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