asked on

Case sensitive SQL search

I have a SQL server 2000 installation which is setup without a Case Sensitive collation. I was wondering does anyone know how I can perform a case sensitive search on the following:

SELECT *
FROM Descrip
WHERE Col002 = 'Y71Xe' AND Col003 = 'P'

I have tried the following, but cannot seem to get it to work:

SELECT *
FROM Descrip
WHERE (Col002 = CONVERT(varbinary, 'Y71Xe')) AND (Col003 = 'P')

In addition I will need to perform a table join with case sensitivity on the joining column.

Any Ideas? Cheers

wsteegmans

Try something like this:

for Col002 declared as varchar(xx)
select * from Descrip where CAST(Col002 as BINARY(35))=CAST( 'Y71Xe' as BINARY(35) )

for Col002 declared as nvarchar(xx)
select * from Descrip where CAST(Col002 as BINARY(35))=CAST( N'Y71Xe' as BINARY(35) )

About your JOIN-question: maybe, because you need a lot of Case Senstive features, isn't it an option to upgrade your SQL-Server to Case Sensitive mode? You stay generating 'work-arrounds'.

paulsidebottom

ASKER

The work I am doing will involve multiple remote sites, running live databases, unfortuantely it will not be possible to take the database down to reinstall SQL Server. However, I do not anticpiate that this query will be executed often.

Onto the code...

The query you gave me worked brilliantly, one final question (and the points are yours ;-)).

I have another table (TermV3) which contains a description column and a TermID column.

When querying the description column (with a LIKE query) I wish to pass the associated TermID into the query you previously helped with ie. in place of the code Y71Xe held in the termID column (presumably I need to join the tables on the the TermID column).

Any help greatly appreciated.

ASKER CERTIFIED SOLUTION

wsteegmans

membership

This solution is only available to members.

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

Start Free Trial

paulsidebottom

ASKER

Thanks. I was just about to close this question as I figured it out (once brain was in gear!). The query I used was:

SELECT TermV3.TERM60, Descrip.ReadCode
FROM Descrip INNER JOIN
TermV3 ON Descrip.TermID = TermV3.TERMID AND CAST(Descrip.TermID AS BINARY(5)) = CAST(TermV3.TERMID AS BINARY(5))
WHERE (Descrip.PreferredOrSynonym = 'P') AND (TermV3.TERM60 LIKE '%Sore Throat%')

thanks for all your help.

wsteegmans

You're welcome, and thx for the points ;-)