Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cgrego
Starting Member
3 Posts |
 Posted - 2012-01-20 : 11:21:14
|
| I'm the administrator of an ftp site. I have a spreadsheet (.csv file) that contains the names and information of all files that are uploaded to the site. While some of the filenames are static (e.g., fileA, fileB) others contain dynamic data such as a unique date/timestamp or random cycle number as part of their filename that varies from file to file (e.g., fileC_20120120, fileD_20120121) and therefore cannot be hardcoded into the spreadsheet. Instead, I need to wildcard these filenames in the spreadsheet (e.g. fileC_%, fileD_%) in order to account for the dynamic portion of the filename. The problem I'm having is that whenever a dynamic filename is uploaded and I attempt to query the data from the spreadsheet it's always interpreting the wildcard character % as a literal and therefore not matching the correct pattern. My SQL query is as follows: SELECT * FROM OB_FileUpload_Test.csv WHERE FN_IN LIKE %FS_FILE_NAME%FN_IN represents the filename in the .csv file.%FS_FILE_NAME% represents the filename uploaded.My question is how can I build a SQL query to match the filename wildcard values in the .csv file? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-21 : 07:22:18
|
If FN_IN is the partial name, your query should be more like:SELECT * FROM OB_FileUpload_Test.csv WHERE FS_FILE_NAME LIKE '%FN_IN%' That said, are you using SQL server? I am asking because the query you posted would not have parsed correctly in T-SQL. |
 |
|
|
cgrego
Starting Member
3 Posts |
Posted - 2012-01-23 : 10:50:52
|
| Thank you for the reply. I tried your approach but unfortunately it errored off. The field (column) name in the spreadsheet is FN_IN, while %FS_FILE_NAME% represents the filename received, not the other way around. According to the SQL documentation, the value that you're querying against in a file must always come first in the WHERE clause. Apparently you can't transpose the two values. I need a way to expand the wildcard character % in the spreadsheet to match the date/time or other variable parts of the filename received.This SQL query is being run from an advanced workflow in GlobalSCAPE EFT Server Enterprise 6.3. It doesn't provide the version of SQL in any of the help documentation. |
|
|
|
cgrego
Starting Member
3 Posts |
Posted - 2012-02-02 : 09:16:01
|
| Finally got it working. Simply inverted the arguments in the SQL query:SELECT * FROM OB_FileUpload_Test.csv where '%FS_FILE_NAME%' LIKE FN_INThis technique allows you to embed wildcards in a file and then match those patterns with any given value. The SQL wildcard character I used in the file is '%' because it represents a string of one or more characters. However, you could also use any of the other wildcards depending upon pattern matching requirements. |
|
|
|
|
|
|
|
|
Querying Wildcards from a File