case sensitivity in mssql

[Former Member]

i have a table with lower case and upper case

for eg;

Bookid Book_Title keyword

001 New Fashion Fashion

002 GOURMET GOURMET

003 orange orange

when select from the table , i face the problem

select from book_tab where

book_title='Orange' no result

select from book_tab where

book_title='ORANGE' no result

select from book_tab where

book_title='orange' return 1 row

what i can do is to make all uppercase..yet ...i dun want to do this...

what is the options for this...

[Former Member]

hi,

Try this sql stmt

select LOWER(book_title) as expr1 , LOWER(keyword)

from book_tab

where LOWER(book_title)=LOWER('ORANGE')

Regards,

Beevin.

[Former Member]

i have try the sql stmt statment....

the function - > LOWER / UPPER is not allowed....(err msg)

[Former Member]

Can You post ur error msg

Regards,

Joseph

[Former Member]

err msg: the function LOWER / UPPER is not allowed....(err msg)

anybody pls tell me if they can use the statment to in coding by include the lower / upper function to select

select UPPER(name) from student_tab;

try this in the coding....

MS-SQL

no doubt , i can run in sql query analyzer..but no in coding...

Message was edited by:

yzme yzme

[prashil]

Hi Yzme,

Usually The MSSQL are not case sensitive by default.

Try to check 'case-sensitive instance of SQL Server' in SQL Server books online.

Thanks,

Prashil

[Former Member]

i switch between this ??

The following query will make CASE Insensitive:

UPDATE Syscolumns SET collation = 'SQL_Latin1_General_CP1_CI_AS' WHERE name = '<column name>'

AND id = object_id('<table name>')

UPDATE Syscolumns SET collation = 'SQL_Latin1_General_CP1_CI_AS' WHERE name = 'bookid','book_title','keyword'

AND id = object_id('book')

or

UPDATE Syscolumns SET collation = 'SQL_Latin1_General_CP1_CI_AS' WHERE name = 'bookid,book_title,keyword'

AND id = object_id('book_tab')

if i am using LOWER while select it will look like this

select LOWER(book_title) as expr1 , LOWER(keyword)

from book_tab

where LOWER(book_title='orange')

Message was edited by:

yzme yzme