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
LVL 1
paulsidebottomAsked:
Who is Participating?
 
wsteegmansConnect With a Mentor Commented:
Try something like this ...
** I was not able to test it, so just give it a try **

Select * from Descrip, TermV3
Where TermV3.Description LIKE '%Something%'
And CAST(Descrip.TermID as BINARY(35)) = CAST(TermV3.TermID as BINARY(35))
0
 
wsteegmansCommented:
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'.
0
 
paulsidebottomAuthor Commented:
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.
0
 
paulsidebottomAuthor Commented:
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.
0
 
wsteegmansCommented:
You're welcome, and thx for the points ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.