Search for percent signs using like query

Question

Post reply

Search for percent signs using like query

K Currie

SSCommitted

Points: 1662
More actions
May 11, 2007 at 3:39 pm

#103360

This should be an easy one for all you Pros!
I'm trying to search for the actual percent sign in a field using a like statement. Normally, the query like would say:
where note like '%sample%'
I want to look for the actual % sign. I know you can't do:
where note like '%%%'
I was thinking about using the ASCII character 37, but don't know if this would work and if so, exactly how you write it:
where note like &CHAR#37

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

bdohmen SSCrazy Points: 2996 More actions · Answer 1

bdohmen

SSCrazy

Points: 2996

May 11, 2007 at 3:48 pm

#705998

Try

Like '%[%]%'

Brian

K Currie SSCommitted Points: 1662 More actions · Answer 2

This worked, thanks!

Now for my side question:

What if my field only had a Percent Sign in it?

='[%]' and =[%]

do not work.

SQLBill SSC Guru Points: 51440 More actions · Answer 3

In the BOL, use the Index search, look up LIKE. Choose the option for the Transact SQL entry. Scroll down until you see Pattern Matching with the Escape Clause.

-SQLBill

bdohmen SSCrazy Points: 2996 More actions · Answer 4

You only escape out the character if you are using LIKE.

='%'

Brian

K Currie SSCommitted Points: 1662 More actions · Answer 5

Thanks, again.

My field was a text field, so I couldn't use = '%'

Server: Msg 306, Level 16, State 1, Line 1

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Try

LIKE '/%' ESCAPE '/'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass