Using SQL code to read a filename

Teedee

5 pts.

Tags:

SQL

SQL Query

SQL statements

Can you write a SQL query that will read PDF filename from a dir and then match the filename to a data element in a table having the same name? For instance, the filename is 12345.pdf where 12345 is actually an invoice number. Can the query read the filename and then locate the same invoice number from within a column in a table that contains a list of invoice numbers?

Asked: July 11, 2009 6:04 AM Last updated: December 5, 2014 7:38 PM

Answer Wiki

Last updated: December 5, 2014 7:38 PM GMT

Michael Tidmarsh62,160 pts.
History
Contributors
- Ordered by most recent
- Michael Tidmarsh62,160 pts.
- Denny Cherry68,390 pts.

Thanks. We'll let you know when a new response is added.

Yes you can do this. You can use xp_dirtree to get a list of files in a folder and drop the returned recordset into a table then loop through the rows in the table processing the filenames.

—————-

Hi, not to put too fine of a point on this but I would disagree with this answer. The question is can one use SQL to access a PC directory structure. SQL can only access RDBMS data. The suggested approach is not truly using SQL; where SQL consists of SELECT, CREATE, REVOKE, etc.

Discuss This Question: 2 Replies

carlosdl Jul 30, 2009 1:59 PM GMT

I guess Meandyou is right, but if the question had been answered paying strict attention to the words, I guess the answer would have been a simple NO. Maybe the question asker did not use the proper words for what he needed. Who knows.

84,495 pointsBadges:

report
Denny Cherry Jul 31, 2009 7:54 PM GMT

The odds of someone knowing the difference between SQL, T/SQL, PL/SQL etc is minimal in most cases. If they had asked for an ANSI SQL solution to the problem, then obviously there isn't one. It is extremely common for people who are working with databases (SQL Server, DB2, etc) to simply refer to the language they use as SQL. Is this accurate? No. Is it common place? Yes. Does that mean that the users should be beat down with semantics? No. People go to forum sites to get an answer to a question that is causing them problems. In a lot of cases English isn't there first language (and I have no idea if that is the case here) so trying to get across the concept that ANSI SQL and SQL are different and that they should be references as such is a loosing battle which will simply make the users not come back. If this person needed more clarification they can simply ask for more clarification. Either this person got the answer they needed, they didn't need the answer any more, or they forgot they posted the question here.

68,390 pointsBadges:

report