SQL Wildcard within data

Question

My situation is a bit odd. I have generated filenames inserted into a database that are to be matched with downloaded files later, however the downloaded files contain a random ID in the middle of their name.

If I include the wildcard characters on the generated filename that's inserted into the database, is there anyway to compare that against the actual downloaded file?

Generated name example: just-a-file-name-and-a-suffix.mp4
Actual downloaded file: just-a-file-name-51935-and-a-suffix.mp4

I have zero way to actually know what those 5 digits will be. I only know that there will be 5 digits. Ideally I'd insert the generated name into the database something like just-a-file-name-%-and-a-suffix.mp4 and use the LIKE keyword in a SQL query but it doesn't work that way.

Is there anything I can do to solve this and get a match?

Are those 5 digits separated by `-`? – Radagast Aug 12 '20 at 01:30 — Radagast, Aug 12 '20 at 01:30

score 0 · Answer 1 · answered Aug 12 '20 at 01:23

you can use substring, replace and some other functions if you like. Following is an example of using replace and creating a function for it.

` CREATE Function [dbo].[RemoveNumericCharacters](@Temp VarChar(1000)) Returns VarChar(1000) AS Begin

Declare @NumRange as varchar(50) = '%[0-9]%'
While PatIndex(@NumRange, @Temp) > 0
    Set @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1, '')

Return @Temp

End and you can use it like so

SELECT dbo.RemoveNumericCharacters FROM TARGET_TABLE ` taken from following post -- Remove numbers from string sql server

Note you be passing on your file string to the function and it will return the file name without the numbers in it.

Thanks

SQL Wildcard within data

1 Answers1