SQL string sensitive search

pzozulka
pzozulka used Ask the Experts™
on
declare @reason varchar(4000)

set @reason = 'Test, Reason'

select *
from Transactions t
where t.UserReason in (select item from dbo.CreateStringTable(@reason))

Open in new window


I have a stored procedure which accepts @reason as an incoming string parameter, but for the purposes of this post, I declred @reason manually, and assigned it some test values. In the stored procedure, we use a function "CreateStringTable", which accepts a string of comma separated values, and returns them as a column in a table.

To avoid confusion, the query above can be seen as:
select *
from Transactions t
where t.UserReason in (select columnA from TableA)

Open in new window



How do I do a case-sensitive search of this? In other words, if columnA only contains the word 'Test', and t.UserReason contains only the word 'test', no records should be returned.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
you can use a Case-Sensitive collation:
Select * from a_table where attribute = 'k' COLLATE Latin1_General_CS_AS 

Open in new window

Author

Commented:
I get an error message: Incorrect syntax near the keyword 'COLLATE'. Query below:
Select * from a_table 
where attribute in (select columnA from TableA) COLLATE Latin1_General_CS_AS 

Open in new window

Business Intelligence Developer and Analyst
Top Expert 2015
Commented:
You can use case sensitive search using collate in your statement like.

select * from Transactions t where t.UserReason  COLLATE Latin1_General_CS_AS in (select columnA from TableA)


or

if you don't want to right every time this in your query you can alter your column of table to be case sensetive.

alter table Transactions
UserReason <datatypeof column>
COLLATE Latin1_General_CS_AS

if data type id varchar (50) and column allows null then

alter table Transactions
UserReason varchar(50) null
COLLATE Latin1_General_CS_AS



but it will do every time case sensitive compare.

hope it will help you

Author

Commented:
Thanks Arif, that worked out great.

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