Search for ampersand in SQL Server data

mcoleson
mcoleson used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2008
Awarded 2008
Commented:

select * from myTable where myColumn like '%[%]%'
Top Expert 2010

Commented:
Try:

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

if that doesn't work:

Select myColumn from mytable where charindex('&', myColumn) > 0
Top Expert 2010

Commented:
mcoleson said:
>>I would like to find rows that have ampersands in a column.

Are you looking for ampersand, which is &, or the % symbol?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
LIKE '%[%]%'

Author

Commented:
Sorry, I said ampersand. I meant the percent symbol.
Top Expert 2010

Commented:
mcoleson,

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

Regards,

Patrick
Awarded 2008
Awarded 2008

Commented:
My thoughts exactly....

Commented:
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)

Top Expert 2010

Commented:
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 :)

Author

Commented:
OK, I'll gladly split the points. I guess I acted too quickly.

Author

Commented:
Experts,
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!!
Top Expert 2010

Commented:
mcoleson,

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.

Regards,

Patrick

Commented:
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.

Author

Commented:
This works perfectly. Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial