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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
wsteegmansCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.