asked on

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

ASKER CERTIFIED SOLUTION

chapmandew

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Patrick Matthews

Try:

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

if that doesn't work:

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

Patrick Matthews

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

Are you looking for ampersand, which is &, or the % symbol?

SOLUTION

jogos

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

mcoleson

ASKER

Sorry, I said ampersand. I meant the percent symbol.

Patrick Matthews

mcoleson,

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

Regards,

Patrick

chapmandew

My thoughts exactly....

jogos

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 Matthews

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

mcoleson

ASKER

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

mcoleson

ASKER

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!!

Patrick Matthews

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

jogos

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.

mcoleson

ASKER

This works perfectly. Thank you