Solved

Case sensitive SQL search

Posted on 2003-11-13
5
730 Views
Last Modified: 2010-04-17
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
0
Comment
Question by:paulsidebottom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9738633
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
 
LVL 1

Author Comment

by:paulsidebottom
ID: 9738716
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
 
LVL 7

Accepted Solution

by:
wsteegmans earned 125 total points
ID: 9738861
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
 
LVL 1

Author Comment

by:paulsidebottom
ID: 9738879
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
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9738893
You're welcome, and thx for the points ;-)
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question