Looking for wildcard characters as literal strings

[SQLJoe]

How can I search for literal wildcard characters in a string? For example, SQL interprets an underscore as any single character, but I need to find an actual underscore character.

I need to find strings matching this format, where ONLY the % sign is a wildcard:

%_US_ %_%_%.pdf

Thanks!

 

[gmmastros]

Put square brackets around the underscores.

[tt]Where Blah Like '%[_]US[_] %[_]%[_]%.pdf'[/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

Let me point out one 'bad' thing about your search string. Having the wildcard in front of the string will keep any index from being used and the query will have to search every single row. If you have lots of rows...that could be a big performance hit.

You may not be able to improve it...so just be aware the query may really slow things down.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLJoe]

Thanks both for the valuable info, but I'm still having issues.

I've changed my syntax to:
where documentname like '%[_]US[_]%[_]%[_]%.pdf'

But curiously, this doesn't pick up document names such as '5_US_KR_08_12.pdf' (the single quotes are actually part of the string)

But when I pare it down a bit, this works fine:
where documentname like '%[_]US[_]%[_]%[_]%'

So what am I doing wrong adding .pdf to the end?:
where documentname like '%[_]US[_]%[_]%[_]%.pdf'

 

[gmmastros]

When you do a like comparison, and you do NOT have a percent at the end, then SQL Server interprets that to mean, "where the data ends with .pdf". Since you have a single quote at the end of your data, the data does not end with .pdf, it actually ends with ".pdf'".

There are a couple ways you can handle this.

where documentname like '%[_]US[_]%[_]%[_]%.pdf[!]''[/!]'

or

where documentname like '%[_]US[_]%[_]%[_]%.pdf%'

The first example causes the match where the data ends with .pdf' and the second example will match whether the .pdf is at the end or not.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLJoe]

Oops, I should have caught that. Wasn't paying attention. Thanks!