SQL UNION in the form of a WHILE LOOP?

Hello,

I have a simple query that extracts pattern indexes from a data field.  The problem is that the pattern could appear thousands of times.  So I repeated my query about 1000 times, placing a union between each select statement, and incrementing the PATINDEX I am looking for by 1.  Also, the results need to be placed in a temp table.  

SELECT      CLNDR_ID, CLNDR_NAME,
      PATINDEX('%1(D|%',CLNDR_DATA) AS NWD_INDEX
INTO      #TEMP_NWD
FROM      CALENDAR
            UNION ALL
SELECT      CLNDR_ID, CLNDR_NAME,
      PATINDEX('%2(D|%',CLNDR_DATA) AS NWD_INDEX
FROM      CALENDAR
            UNION ALL
SELECT      CLNDR_ID, CLNDR_NAME,
      PATINDEX('%3(D|%',CLNDR_DATA) AS NWD_INDEX
FROM      CALENDAR

ETC, ETC, ETC (INCREMENTING THE FIRST NUMBER IN THE PATINDEX FUNCTION EACH TIME, UP TO 1000)

SELECT * FROM  #TEMP_NWD
WHERE NWD_INDEX IS NOT NULL

DROP TABLE  #TEMP_NWD


I am looking for a way to populate my temp table without having to repeat a UNION 1000 times. I cant figure out a way to do this in an SQL stored procedure where I use a while loop, or something like that.  But there MUST be a way.

500 points and my everlasting grattitude if you can solve this one!!!

Thanks very much in advance!

Dan Ballanco
dballancoAsked:
Who is Participating?
 
LowfatspreadCommented:
does this assist you...?

drop table #calendar
drop table #temp
drop table #temp_nwd

Select 1 as clndr_id,'AAA' as clndr_name,
'0||Exceptions()( (0||0(d|37669)()) (0||1(d|37767)()) (0||2(d|37806)()) (0||3(d|37952)()) (0||4(d|37953)()) (0||5(d|37980)()) (0||6(d|37981)()) (0||7(d|37987)()) (0||8(d|38138)()) (0||9(d|38172)()) (0||10(d|38173)()) (0||11(d|38236)()) (0||12(d|38316)()) (0||13(d|38317)()) (0||14(d|38345)()) (0||15(d|38346)()) (0||16(d|38353)()) (0||17(d|38354)()) (0||18(d|38355)()) (0||19(d|38502)()) (0||20(d|38537)()) (0||21(d|38600)())))))'
as clndr_data
 into #calendar
select * into #temp_nwd from #calendar where 0=1
/*
SELECT     CLNDR_ID, CLNDR_NAME,
     PATINDEX(X.Pattern,CLNDR_DATA) AS NWD_INDEX
INTO     #TEMP_NWD
FROM     #CALENDAR
 , (select '%1000(D|%'  as Pattern
     union select '%[1-9][0-9][0-9](D|%'  
      union select '%[1-9][0-9](D|%'
       union select '%[1-9](D|%' ) as x
*/
Declare @max int
Declare @pos int
Declare @I int
Declare @loop char(1)
declare @test varchar(8000)

SELECT  c.*,Identity(int,1,1) as rowid
INTO     #TEMP
FROM     #CALENDAR as c
Where Clndr_data like '%[0-9](D|%'
set @max=@@rowcount
Set @i=1
While @i<=@max
begin
   Select @Test = Clndr_data
      from #temp
     where rowid=@i
   Set @pos=1
   Set @loop='Y'
   
   While @loop='Y'
   Begin
   
   Insert into #Temp_nwd
    Select CLNDR_ID, CLNDR_NAME,
           Dateadd(d,
           convert(int,Substring(@TEst,PATINDEX('%[0-9](D|%',@test)+4,5))
         ,'19000101')
      FROM #Temp
     Where Rowid=@i  
    Select @test=Right(@test,Datalength(@test) - PATINDEX('%[0-9](D|%',@test)-5)  
    select @loop = case when Patindex('%[0-9](D|%',@test) > 0 then 'Y' else 'N' end
   end
   set @i=@i+1
end
select * from #temp_nwd
0
 
Vadim RappCommented:
it's possible to offer some tricky string manipulations, but I'm sure that they would be much better with seeing several sample rows - specifically, what goes before (D|
0
 
Vadim RappCommented:
...here's one way:

case
when isnumeric (substring(CLNDR_DATA, PATINDEX('%(D|%',CLNDR_DATA) -4, 1)) =1
and   isnumeric (substring(CLNDR_DATA, PATINDEX('%(D|%',CLNDR_DATA) -3, 1)) =1
and   isnumeric (substring(CLNDR_DATA, PATINDEX('%(D|%',CLNDR_DATA) -2, 1)) =1
and   isnumeric (substring(CLNDR_DATA, PATINDEX('%(D|%',CLNDR_DATA) -1, 1)) =1
then  PATINDEX('%(D|%',CLNDR_DATA) -4

when isnumeric (substring(CLNDR_DATA, PATINDEX('%(D|%',CLNDR_DATA) -3, 1)) =1
and   isnumeric (substring(CLNDR_DATA, PATINDEX('%(D|%',CLNDR_DATA) -2, 1)) =1
and   isnumeric (substring(CLNDR_DATA, PATINDEX('%(D|%',CLNDR_DATA) -1, 1)) =1
then  PATINDEX('%(D|%',CLNDR_DATA) -3

when isnumeric (substring(CLNDR_DATA, PATINDEX('%(D|%',CLNDR_DATA) -2, 1)) =1
and   isnumeric (substring(CLNDR_DATA, PATINDEX('%(D|%',CLNDR_DATA) -1, 1)) =1
then  PATINDEX('%(D|%',CLNDR_DATA) -2

when isnumeric (substring(CLNDR_DATA, PATINDEX('%(D|%',CLNDR_DATA) -1, 1)) =1
then  PATINDEX('%(D|%',CLNDR_DATA) -1




0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
LowfatspreadCommented:
why the null test?

and why are you retrieving duplicate results...

e.g.

103(D

will have a hit
for

3(D
and
103(D

what are you actually trying to establish?
0
 
Vadim RappCommented:
I think the intent is to find where the number begins preceding (D

abc123(D   -> 3
ab1234(D -> 2
0
 
Vadim RappCommented:
abc123(D   -> 4
ab1234(D -> 3
0
 
LowfatspreadCommented:
sample data would be useful

SELECT     CLNDR_ID, CLNDR_NAME,
     PATINDEX(X.Pattern,CLNDR_DATA) AS NWD_INDEX
INTO     #TEMP_NWD
FROM     CALENDAR
 , (select '%1000(D¦%'  as Pattern
     union select '%[1-9][0-9][0-9](D¦%'  
      union select '%[1-9][0-9](D¦%'
       union select '%[1-9](D¦%' ) as x
0
 
dballancoAuthor Commented:
I am having difficulty translating the responses so far into something that applies to my situation. Let me try to explain better with some examples. Sorry about the length/formatting... it looks much better in Query Analyzer:


***WHAT I AM TRYING TO DO:
Extract the 5-character string (serial date) which follows "#(d|" from a large text field in SQL Server (where '#' is the sequence number I am trying to increment through a loop). Then I convert that number to a date using a formula. There can be an infinite amount of values (the example row has 21, but other rows could have different numbers of values. I just cycle through 1000 times, thinking that is more than enough. Then, I eliminate the NULLS in the second query.


***THE QUERY THAT IS EXTRACTING DATA:
select clndr_id, clndr_name,case when substring(clndr_data, (PATINDEX('%1(d|%',clndr_data)+4), 5) not like '%a%' then DATEADD(day, cast(substring(clndr_data, (PATINDEX('%1(d|%',clndr_data)+4), 5) as int), '12/30/1899') ELSE NULL END AS NON_WORK_DATE into #TEMP_NWD FROM calendar UNION ALL
select clndr_id, clndr_name,case when substring(clndr_data, (PATINDEX('%2(d|%',clndr_data)+4), 5) not like '%a%' then DATEADD(day, cast(substring(clndr_data, (PATINDEX('%2(d|%',clndr_data)+4), 5) as int), '12/30/1899') ELSE NULL END AS NON_WORK_DATE FROM calendar UNION ALL
select clndr_id, clndr_name,case when substring(clndr_data, (PATINDEX('%3(d|%',clndr_data)+4), 5) not like '%a%' then DATEADD(day, cast(substring(clndr_data, (PATINDEX('%3(d|%',clndr_data)+4), 5) as int), '12/30/1899') ELSE NULL END AS NON_WORK_DATE FROM calendar UNION ALL
select clndr_id, clndr_name,case when substring(clndr_data, (PATINDEX('%4(d|%',clndr_data)+4), 5) not like '%a%' then DATEADD(day, cast(substring(clndr_data, (PATINDEX('%4(d|%',clndr_data)+4), 5) as int), '12/30/1899') ELSE NULL END AS NON_WORK_DATE FROM calendar UNION ALL
select clndr_id, clndr_name,case when substring(clndr_data, (PATINDEX('%5(d|%',clndr_data)+4), 5) not like '%a%' then DATEADD(day, cast(substring(clndr_data, (PATINDEX('%5(d|%',clndr_data)+4), 5) as int), '12/30/1899') ELSE NULL END AS NON_WORK_DATE FROM calendar UNION ALL
select clndr_id, clndr_name,case when substring(clndr_data, (PATINDEX('%6(d|%',clndr_data)+4), 5) not like '%a%' then DATEADD(day, cast(substring(clndr_data, (PATINDEX('%6(d|%',clndr_data)+4), 5) as int), '12/30/1899') ELSE NULL END AS NON_WORK_DATE FROM calendar UNION ALL
select clndr_id, clndr_name,case when substring(clndr_data, (PATINDEX('%7(d|%',clndr_data)+4), 5) not like '%a%' then DATEADD(day, cast(substring(clndr_data, (PATINDEX('%7(d|%',clndr_data)+4), 5) as int), '12/30/1899') ELSE NULL END AS NON_WORK_DATE FROM calendar UNION ALL
select clndr_id, clndr_name,case when substring(clndr_data, (PATINDEX('%8(d|%',clndr_data)+4), 5) not like '%a%' then DATEADD(day, cast(substring(clndr_data, (PATINDEX('%8(d|%',clndr_data)+4), 5) as int), '12/30/1899') ELSE NULL END AS NON_WORK_DATE FROM calendar UNION ALL
select clndr_id, clndr_name,case when substring(clndr_data, (PATINDEX('%9(d|%',clndr_data)+4), 5) not like '%a%' then DATEADD(day, cast(substring(clndr_data, (PATINDEX('%9(d|%',clndr_data)+4), 5) as int), '12/30/1899') ELSE NULL END AS NON_WORK_DATE FROM calendar UNION ALL
select clndr_id, clndr_name,case when substring(clndr_data, (PATINDEX('%10(d|%',clndr_data)+5), 5) not like '%a%' then DATEADD(day, cast(substring(clndr_data, (PATINDEX('%10(d|%',clndr_data)+5), 5) as int), '12/30/1899') ELSE NULL END AS NON_WORK_DATE FROM calendar UNION ALL
select clndr_id, clndr_name,case when substring(clndr_data, (PATINDEX('%11(d|%',clndr_data)+5), 5) not like '%a%' then DATEADD(day, cast(substring(clndr_data, (PATINDEX('%11(d|%',clndr_data)+5), 5) as int), '12/30/1899') ELSE NULL END AS NON_WORK_DATE FROM calendar UNION ALL
select clndr_id, clndr_name,case when substring(clndr_data, (PATINDEX('%12(d|%',clndr_data)+5), 5) not like '%a%' then DATEADD(day, cast(substring(clndr_data, (PATINDEX('%12(d|%',clndr_data)+5), 5) as int), '12/30/1899') ELSE NULL END AS NON_WORK_DATE FROM calendar

            --...on and on to 1000


select * from #TEMP_NWD
where NON_WORK_DATE IS NOT NULL
order by 1,2,3



***SAMPLE QUERY OUTPUT
ID      NAME                        NON_WORK_DATE
592      7 Day Workweek (M,T,W,TH,F,S,S)      2003-05-26 00:00:00.000      
592      7 Day Workweek (M,T,W,TH,F,S,S)      2003-07-04 00:00:00.000      
592      7 Day Workweek (M,T,W,TH,F,S,S)      2003-11-27 00:00:00.000      
592      7 Day Workweek (M,T,W,TH,F,S,S)      2003-11-28 00:00:00.000      
592      7 Day Workweek (M,T,W,TH,F,S,S)      2003-12-25 00:00:00.000      
592      7 Day Workweek (M,T,W,TH,F,S,S)      2003-12-26 00:00:00.000      
592      7 Day Workweek (M,T,W,TH,F,S,S)      2004-01-01 00:00:00.000      
592      7 Day Workweek (M,T,W,TH,F,S,S)      2004-05-31 00:00:00.000      
592      7 Day Workweek (M,T,W,TH,F,S,S)      2004-07-04 00:00:00.000      
592      7 Day Workweek (M,T,W,TH,F,S,S)      2004-07-05 00:00:00.000      
592      7 Day Workweek (M,T,W,TH,F,S,S)      2004-09-06 00:00:00.000      
592      7 Day Workweek (M,T,W,TH,F,S,S)      2004-11-25 00:00:00.000      


***SAMPLE ROW FOR THE 'CLNDR_DATA' COLUMN:
(0||Exceptions()( (0||0(d|37669)()) (0||1(d|37767)()) (0||2(d|37806)()) (0||3(d|37952)()) (0||4(d|37953)()) (0||5(d|37980)()) (0||6(d|37981)()) (0||7(d|37987)()) (0||8(d|38138)()) (0||9(d|38172)()) (0||10(d|38173)()) (0||11(d|38236)()) (0||12(d|38316)()) (0||13(d|38317)()) (0||14(d|38345)()) (0||15(d|38346)()) (0||16(d|38353)()) (0||17(d|38354)()) (0||18(d|38355)()) (0||19(d|38502)()) (0||20(d|38537)()) (0||21(d|38600)())))))
0
 
LowfatspreadCommented:
do you actually mean a text datatype?

which version of sql server are you using ?

sorry replace 19000101 with 18991230 in the dateadd

i got 22 result from your test example not 21...

if the column type is Nchar or Ntext then

 Select @test=Right(@test,Datalength(@test) - PATINDEX('%[0-9](D|%',@test)-5)  
would be
 Select @test=Right(@test,(Datalength(@test)/2) - PATINDEX('%[0-9](D|%',@test)-5)  



0
 
Vadim RappCommented:
I think the sample row that you provided calls for client-side implementation. Some vbscript can very easily handle this with a function Split() - so much easier than TSQL
0
 
Vadim RappCommented:
(inadvertently pressed "enter")

...solution, that, unless you have millions of rows to process, it's unreasonable not to do it.
0
 
dballancoAuthor Commented:
LowFatSpread.... Just want to let you know you nailed it!!!  Thank you so much, you did what would have taken me weeks to figure out!  The points are all yours...


-Dan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.