Search for ampersand in SQL Server data

I would like to find rows that have ampersands in a column.
Normally, I would write this:
Select myColumn from mytable where myColumn like '%SEARCHSTRING%'
But if I put the ampersand in there, obviously it doesn't work: (returns all rows)
Select myColumn from mytable where myColumn like '%%%'

Thanks for your help
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


select * from myTable where myColumn like '%[%]%'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:

Select myColumn from mytable where myColumn like '%&%'

if that doesn't work:

Select myColumn from mytable where charindex('&', myColumn) > 0
Patrick MatthewsCommented:
mcoleson said:
>>I would like to find rows that have ampersands in a column.

Are you looking for ampersand, which is &, or the % symbol?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

LIKE '%[%]%'
mcolesonAuthor Commented:
Sorry, I said ampersand. I meant the percent symbol.
Patrick MatthewsCommented:

Why did you select jogos's post when chapmandrew had an identical suggestion a bit earlier?


My thoughts exactly....
Indeed my post wasn't the first (and also didn't comment on the ampersand-% mixup). Nearly simultainous comments it would be fair to
- give it to the first
- or at least share the points (assisted solution)

Patrick MatthewsCommented:
Given that the posts were potentially just a few seconds apart, I think a split would have been fair.

It would be nice to be a party to the split, seeing as I went with the ampersand, but not necessary :)
mcolesonAuthor Commented:
OK, I'll gladly split the points. I guess I acted too quickly.
mcolesonAuthor Commented:
Is there a way to go back and split points? Sorry, I don't see how to do that after accepting the solution.
I really do appreciate all your very fast responses!!
Patrick MatthewsCommented:

No worries--it happens :)

The thing to do is to use the 'request attention' link just under your original question text, and dash out a note
saying that you'd like this question reopened.  The Mods usually respond to these within 24 hours.  Then, once
the question is reopened, you can enter a split.


I don't mind the reopening.  But one time you will get a few points more, another time you get a few less where you worked hard for and a some bloke changes 2 characters in a sql and get your points.

I do it for fun.
mcolesonAuthor Commented:
This works perfectly. Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.