Problem with Case Sensitivity in SQL Server db

[JimP]

I'm working with a SQL Server db with case sensitivity active - trying to
trap a value in a query or code.

e.g. the value I am trying to trap for is the word "Red" in a column, to
retrieve the row

WHERE = "Red" (works)
WHERE BETWEEN "red" and "RED" ( fails)

I can't be sure of the case sensitivity of "Red", could be any combination
of lower and upper case characters - but need to retrieve all rows with the
letters "r", "e", "d".

 

[John W. Vinson]

I'm working with a SQL Server db with case sensitivity active - trying to
trap a value in a query or code.

e.g. the value I am trying to trap for is the word "Red" in a column, to
retrieve the row

WHERE = "Red" (works)
WHERE BETWEEN "red" and "RED" ( fails)

I can't be sure of the case sensitivity of "Red", could be any combination
of lower and upper case characters - but need to retrieve all rows with the
letters "r", "e", "d".

WHERE Ucase([fieldname]) = UCase("Red")

(or lcase for both of course).

Defeats any SQL indexes and forces a full table scan so it may be slow on
large datasets.