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,
JayI 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
--
No comments:
Post a Comment