October 3, 2016 at 1:40 pm
I have a text file that will be imported into a table. Name of table is PHOTOPATH.
The column name is photo and is an nvarchar(50). It will contain data such as :
BZSDZGFY.jpg
CBMCPDNC.jpg
CKXSHRNV.jpg
etc...
I need to write a query that looks like this..
SELECT userid, photoid FROM idcard
WHERE photoid LIKE '%BZSDZGFY.jpg' OR
photoid LIKE '%CBMCPDNC.jpg'
Is there a way that the WHERE statement can go in a loop and read each line ('%NAMEOFJPG.jpg').
I would greatly appreciate any ideas. Not a SQL Developer but trying to learn as fast as I can.
October 3, 2016 at 1:49 pm
Are you asking how to SQL query a text file?
If so, then you canuse OPENROWSET function.
https://msdn.microsoft.com/en-us/library/ms190312.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 3, 2016 at 1:55 pm
jcarranza 23978 (10/3/2016)
I have a text file that will be imported into a table. Name of table is PHOTOPATH.The column name is photo and is an nvarchar(50). It will contain data such as :
BZSDZGFY.jpg
CBMCPDNC.jpg
CKXSHRNV.jpg
etc...
I need to write a query that looks like this..
SELECT userid, photoid FROM idcard
WHERE photoid LIKE '%BZSDZGFY.jpg' OR
photoid LIKE '%CBMCPDNC.jpg'
Is there a way that the WHERE statement can go in a loop and read each line ('%NAMEOFJPG.jpg').
I would greatly appreciate any ideas. Not a SQL Developer but trying to learn as fast as I can.
You're still thinking iterative (row) rather than set-based (column). The WHERE clause applies to the entire column. There is nothing to loop over.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply