SEO

#1 (**permalink**) 02-29-2008, 02:58 AM

Hi,

I have yet to find an answer for this:

I want to do a case-sensitive query using "like" on a table in sql 7.
Currently, "like" performs case-insensitive query.

I understand that you can use the following query in sql 2000:

SELECT *
FROM table_x
WHERE col1 collate SQL_Latin1_General_CP1_CS_AS LIKE '% AVE %'

However, is there a similar method for sql 7?

Any answer would be appreciated.

Thanks,
Jay

#2 (**permalink**) 02-29-2008, 02:58 AM

I assume you mean that you want a case-sensitive search on a
case-insensitive server. Obviously the ideal is to have a sort order that
meets your requirements. You can change the sort-order in 7.0 but only at
the server-level.

This should do it under any sort order:

SELECT col1
FROM Table_X
WHERE col1 LIKE '% AVE %'
AND CAST(SUBSTRING(col1,PATINDEX('% AVE %',col1),5) AS VARBINARY)
= CAST(' AVE ' AS VARBINARY)

--
David Portas
SQL Server MVP
--

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

case-sensitive search in sql 7