Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 933
  • Last Modified:

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
0
mcoleson
Asked:
mcoleson
  • 5
  • 4
  • 3
  • +1
2 Solutions
 
chapmandewCommented:

select * from myTable where myColumn like '%[%]%'
0
 
Patrick MatthewsCommented:
Try:

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

if that doesn't work:

Select myColumn from mytable where charindex('&', myColumn) > 0
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?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

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

Regards,

Patrick
0
 
chapmandewCommented:
My thoughts exactly....
0
 
jogosCommented:
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)

0
 
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 :)
0
 
mcolesonAuthor Commented:
OK, I'll gladly split the points. I guess I acted too quickly.
0
 
mcolesonAuthor 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!!
0
 
Patrick MatthewsCommented:
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
0
 
jogosCommented:
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.
0
 
mcolesonAuthor Commented:
This works perfectly. Thank you
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now