?
Solved

SQL UNION in the form of a WHILE LOOP?

Posted on 2006-06-05
12
Medium Priority
?
1,728 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:dballanco
  • 6
  • 4
  • 2
12 Comments
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 16834960
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 16835114
...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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16835397
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 16835438
I think the intent is to find where the number begins preceding (D

abc123(D   -> 3
ab1234(D -> 2
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 16835445
abc123(D   -> 4
ab1234(D -> 3
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16835469
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
 

Author Comment

by:dballanco
ID: 16842790
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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 16853878
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16853931
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 16856915
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 16856934
(inadvertently pressed "enter")

...solution, that, unless you have millions of rows to process, it's unreasonable not to do it.
0
 

Author Comment

by:dballanco
ID: 16903567
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question