I am attempting to do file searches with SQL. I need to keep the searches within the directory I am doing the comparisons against. When doing a LIKE or NOT LIKE comparison in SQL, the wildcard '%' is used to represent 0, 1, or many characters. Are there ways to exclude or delimit characters involved in that wildcard search? I need to do a comparison like the following:
LIKE 'C:/Data/%.txt'
However, I do not want the '%' to find any records where there is a slash '/' character involved in the wildcard search. I would want it to return examples like:
C:/Data/file1.txt
C:/Data/records_2017.txt
C:/Data/listing#7.txt
But I do NOT want the wildcard to find records with slash in it, because in my situation, it goes beyond the current folder I am interested in doing file searches in:
C:/Data/OtherFolder/file1.txt
C:/Data/System/SomethingElse/records_2016.txt
The above examples would be returned, because everything between the slash after Data and the .txt extension is all free game for the wildcard. I do NOT want the above examples to be returned in my wildcard search.
I tried doing character sets, such as [^/], but it seems to only work to exclude strings BEGINNING with a slash '/' character. I need to prevent wildcard from using a slash ANYWHERE in the string.
You can't do this with a single LIKE, but you could do this:
WHERE MyColumn LIKE 'C:/Data/%.txt'
AND MyColumn NOT LIKE 'C:/Data/%/%.txt'