Disable Case-sensitive in SQL

bapperlo
bapperlo used Ask the Experts™
on
How can I make SQL stop looking at the characters if they are capital letters or not. I need this for a search-function where i use the following query:

SELECT document FROM document WHERE document LIKE '%" & searchword & "%'"

searchword is a variable that I have loaded from the page that activated this page.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
do you mean you want case sensitivity or insensitivity?

SELECT document FROM document WHERE UCASE(document) LIKE '%" & UCASE(searchword) & "%'"

is case insensitive. Is that what you mean?

Mark
Case sensitive shouldn't matter, I've been able to search fine for SQL Server.
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Commented:
And maybe add TRIM to removing white space.

Commented:
case sensitivity is a database variable, you can change it on installation (and maybe on other times).

To solve it:

"SELECT * FROM table WHERE LCase(field) LIKE '%" & Lcase(var) & "%'

Commented:
On SQL Server I think you set it at the install time (at least on 6.5 you did).
Yes, in SQL Server 6.5/7.0/ and 2000 I believe you have to set the sort order and the character set which includes case sensitivity and once that is done the only way to undo it is to rebuild the server.

However, the UCASE function that markhoy suggested should probably do what you want.

Regards, Iguanasan

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial