Interpreting Embedded Wildcards in a File from a SQL Query

[cgrego]

I'm the administrator of an ftp site. I have a spreadsheet (CSV file) that contains the names and information of all files uploaded to the site. While some of the filenames are static (e.g., fileA, fileB) others have a 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. I need to be able to wildcard these filenames in the spreadsheet (e.g. fileC_%, fileD_%) in order to account for the changing part of the filename. The problem I'm having is that whenever one of these types of files is uploaded and I attempt to query it from the spreadsheet it's always interpreting the wildcard character % as a literal and therefore not matching the correct filename 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 value contained in the CSV file.
%FS_FILE_NAME% variable represents the filename uploaded.

My question is how can I build a SQL query to match the filename wildcard values in my CSV file?

For example, if my spreadsheet contains an entry for a filename called TR%.A (FN_IN column) and I receive a file upload called TR20120123.A (%FS_FILE_NAME% variable), I want these values to match and for that row to be selected.

 

[simian336]

If I understand you correctly I think your looking for dynamic sql...

declare @FS_FILE_NAME varchar(255)
declare @sql varchar(1000)

set @FS_FILE_NAME = 'TR%.A'
set @sql = 'SELECT * FROM OB_FileUpload_Test.csv WHERE FN_IN LIKE ' + @FS_FILE_NAME

print @sql

exec @sql


Simi

 

[simian336]

oops forgot the quotes

declare @FS_FILE_NAME varchar(255)
declare @sql varchar(1000)

set @FS_FILE_NAME = 'TR%.A'
set @sql = 'SELECT * FROM OB_FileUpload_Test.csv WHERE FN_IN LIKE ''' + @FS_FILE_NAME +''''

print @sql

--exec @sql

Simi

 

[cgrego]

I can't hardcode the 'TR%.A' value for %FS_FILE_NAME% because that variable is always the name of the current file uploaded.

 

[simian336]

I hard coded it as an example, you would pull it from your field.

Simi