asked on

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

slightwv (䄆 Netminder)

Try using square brackets:
select * from inventory where comments like 'Bowl [#] XM21QR%' doesn't work

Steve A

ASKER

Thanks for the reply.
Nope, the square brackets didn't work.

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve A

ASKER

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

Steve A

ASKER

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

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve A

ASKER

Yes, it shows now space or characters in between..

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

Thanks

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial