September 27, 2005 at 2:12 pm
You need a database with this collation : Latin1_General_CS_AS.
Create a table with only two columns:
Create table test (id int, val varchar(9))
Insert about 100 rows with distinct values for the val column.
Create a non-clustered index on the val column.
How can I do a case-insensitive search that used the index.
Voilà,
Regards,
Carl
September 27, 2005 at 2:33 pm
Hello RGR'us,
Finally found what I was looking for: doing an efficient case-insensitive search in a database that is case-sensitive.
Thank's for your help.
Carl
September 27, 2005 at 2:35 pm
Please post the solution so that everyone can benefit from it.
September 27, 2005 at 2:41 pm
Sure...
Just have to modifiy the val column to Latin1_General_CI_AS (case insensitive).
Than rebuild the index on that column (because you have to drop it before modifying the column).
After that just have to issue a query like this one:
SELECT * FROM test WHERE val = 'hElLo'
It will use the index and do a case-insensitive search, returning for example,
id val
--- ---------
1 HELLO
2 hello
3 HeLlO
...
September 27, 2005 at 2:50 pm
You better document that because all searches on that column will be insensitive when the rest of the app is sensitive... that could cause some un-happy users.
September 27, 2005 at 2:53 pm
Hello RGR'us,
Yep... This is why the other solution is also something we will look at (computed columns).
Thanks again,
Carl
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply