Query # signs in a string within Sql Server

How do I find rows in the Sql Server 2016 database that have a '#' in the string value?
I have a inventory table with a comments field with some values like these:
'Bowl # XM21QR  RSeq #1A'
'Bowl # XM21QR  RSeq #2A'
'Bowl # XM21QR  RSeq #3A'

select * from inventory where comments like 'Bowl # XM21QR%' doesn't work
or if I just want to select 1 specific row above doesn't work either..
select * from inventory where comments = 'Bowl # XM21QR  RSeq #1A'

How to query the '#' or a special character.
Thanks
MachinegunnerAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
The spaces are not spaces but rather unprintable characters.  But see for yourself:
select * from inventory where comments like 'Bowl_#_XM21QR%'

Caveat: This is not the solution, as it may return false positives.  Just here to prove a point.
0
 
slightwv (䄆 Netminder) Commented:
Try using square brackets:
select * from inventory where comments like 'Bowl [#] XM21QR%' doesn't work
0
 
MachinegunnerAuthor Commented:
Thanks for the reply.
Nope, the square brackets didn't work.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Scott PletcherConnect With a Mentor Senior DBACommented:
select * from inventory where comments like '%Bowl # XM21QR%'
or maybe even:
select * from inventory where comments like '''Bowl # XM21QR%'
if the quote you show above is literally in the data.

for all rows with a # in that column, then simply:

select * from inventory where comments like '%#%'
0
 
MachinegunnerAuthor Commented:
Thanks for the reply.
What I am really looking for is an exact match of a string value like 'Bowl # XM21QR  RSeq #1A'

select * from inventory where comments = 'Bowl # XM21QR  RSeq #1A';

I just put the LIKE clause examples in there, to try and see if I can even find a match with a '#' in the string.

Thanks again
0
 
MachinegunnerAuthor Commented:
Also, when looking at the datatype for the comments column, I can see it is a nvarchar(300).
So it is probably treating the special characters differently, if it were a varchar(300)?

I tried casting the column as a varchar but that didn't work...
select * from inventory where cast(comments as varchar(300)) = 'Bowl # XM21QR  RSeq #1A';

Also, tried collate and that didn't work either...
select * from inventory where comments collate database_default = 'Bowl # XM21QR  RSeq #1A';

Thanks
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Something else is going on that prevents it from being an exact match.

See if this shows you any space/chars between the /s and the comments column value:

select top (10) '/' + comments + '/'
from inventory
where comments like '%#%'
0
 
MachinegunnerAuthor Commented:
Yes, it shows now space or characters in between..

'/Bowl # XM21QR  RSeq #1A/'
'/Bowl # XM21QR  RSeq #2A/'
'/Bowl # XM21QR  RSeq #3A/'

Thanks
0
All Courses

From novice to tech pro — start learning today.