0

Hello I am trying to create a query that would loop through a table in my sql server, but the problem is I need to filter the query by a certain portion of one of the columns of the table. For example the fields of the table (and an example value) are shown below(table name on top and value directly below):

 PDID      PDDescription      PDFilename      PDDocumentLocation PDDocumentTypeID  
 77234     PDF for FRT       FRT_154327      PD Documents\154487      11

The problem is I need to filter by number section of the PDDocumentlocation, like I would have a set of PD numbers and those numbers correlate with the "PD Documents\####" this section -> ###, is there any way to create a loop query that would pull out these values based on a portion of a column? Also if possible can I reference a folder location in SQL? and for PDDocumentLocation it will always begin with PD Documents if that helps. Thank you! I tried googling iterating through tables but I cant find anything on iterating based on a parameter like a list of numbers and that referring a art of a column. Any help is greatly appreciated.

For example if I have a list of PD Numbers like 12456,13788, 23456 then I want the query to return all the rows that have those numbers and those numbers come from the column "PDDocumentLocation" and all the values in that column start out with 'PD Documents\' and the number list corresponds as such: PD Documents\12456, PD Documents\13788, PD Documents\23456

eto_donna
  • 61
  • 1
  • 1
  • 9
  • Please edit your question and provide examples of what you want to keep and what you want to filter. – Gordon Linoff Mar 12 '16 at 21:10
  • @GordonLinoff just did! I apologize for not clarifying earlier I hope this is better – eto_donna Mar 12 '16 at 21:22
  • Possible duplicate of [Is there a combination of "LIKE" and "IN" in SQL?](http://stackoverflow.com/questions/3014940/is-there-a-combination-of-like-and-in-in-sql) – Ric .Net Mar 12 '16 at 21:25
  • If the location is always in the same format (PD Documents\XXXX) it might be a good idea to create a computed column in your table that contains only the id. If you make it persistent, you can even index it. – James Z Mar 12 '16 at 21:38
  • @JamesZ by computed column do you mean creating a temp table and inserting that column but formatting it where its only the number section? is that possible? because the other thing is I was wondering if a query/stored procedure can pull files from a folder since that is a folder location, will having a computed column hinder that? – eto_donna Mar 12 '16 at 21:44
  • [Computed column](https://msdn.microsoft.com/en-us/library/ms188300.aspx) is part of the existing table, it can be either virtual or persistent and in this case you can do it using substring. – James Z Mar 12 '16 at 21:48

1 Answers1

0

You know prefix (constant root folder) and suffix (list of numbers), location pattern is immutable. Instead of parsing use simpler way - add your root folder to numbers given and get full location name. You don't need to extract a "portion of a column" because you know complete values of this column.

GO
declare @RootFolder varchar(1000) = 'PD Documents\'

declare @FileFolders table
(
  PDName varchar(1000)
)

declare @PDDocumentLocation table
(
  PDDocumentLocation varchar(1000)
)

/* incoming list  */
insert into @FileFolders(PDName)
values ('12456'), ('13788'), ('23456')


/* stored data */
insert into @PDDocumentLocation(PDDocumentLocation)
values 
  ('PD Documents\154487'), 
  ('PD Documents\12456'), 
  ('PD Documents\13788'), 
  ('PD Documents\13788_'), 
  ('PD Documents\17211')

/* preparing filter */
update f set
  PDName = @RootFolder + f.PDName
from @FileFolders f

select d.*
from @PDDocumentlocation d
where exists
  (
    select 1 from @FileFolders ff 
    where ff.PDName = d.PDDocumentLocation
  ) 
order by d.PDDocumentLocation
GO

and loop through your files with cursor or whatever you need.

Ivan Starostin
  • 8,798
  • 5
  • 21
  • 39
  • thank you for the reply! I have a question if you dont mind so in the script would I need to do an insert every time? like would that be the variable? and so what this does from what I see is format the numbers by inserting them into temp table you created and then the filter filters through the actual table with this data? I apologize for all the questions I am still learning about sql and am new to Loops and formatting, I appreciate all the help! – eto_donna Mar 12 '16 at 22:31
  • You can't pass an array of numbers ['123', '1234', '5435'] to the stored proc. You'll have to put them into table-type argument (insert before invoking SP), or to pass them as long `varchar` string, parse inside SP and insert into table variable or to loop through list of numbers from client-side. – Ivan Starostin Mar 12 '16 at 22:39