SQL Statement: Case sensitive

[WooGor]

When I installed my SQL Server 2000, I was asked to set Case Sensitive. Now,
I got a problem as follow.

When I query the Student name list, my SQL Statement would be:

Sql = “SELECTE * FROM T_Student Where StudentName LIKE '%" & strSearchName
& "%'"

When the user type john or JOHN in the box, namely, strSearchName, it returns
nothing; however, if it is John, it then returns the records that state in
the database.

Question to you: How could I get john or JOHN as the same as it is John,
meaning that turn case sensitive to case insensitive for searching?

Please note that the field StudenName is saved to the database as LastName,FirstName.
For example, Smith,John.

Any prompt response to my question is greatly appreciated.

[David Satz]

you can try:

Sql = "SELECTE * FROM T_Student Where Upper(StudentName) LIKE '%" &
Ucase(strSearchName) & "%'"

notes:
-Ucase assumes you are in vbscript
-be aware that putting Upper(StudentName) in the where clause rather than
StudentName will force the optimizer to do a table scan
--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions
{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------
"WooGor" <zhang5gor@yahoo.com> wrote in message
news:3cb1ba74$1@10.1.10.29...
>
> When I installed my SQL Server 2000, I was asked to set Case Sensitive.
Now,
> I got a problem as follow.
>
> When I query the Student name list, my SQL Statement would be:
>
> Sql = "SELECTE * FROM T_Student Where StudentName LIKE '%" & strSearchName
> & "%'"
>
> When the user type john or JOHN in the box, namely, strSearchName, it
returns
> nothing; however, if it is John, it then returns the records that state in
> the database.
>
> Question to you: How could I get john or JOHN as the same as it is John,
> meaning that turn case sensitive to case insensitive for searching?
>
> Please note that the field StudenName is saved to the database as
LastName,FirstName.
> For example, Smith,John.
>
> Any prompt response to my question is greatly appreciated.

[Andre]

SQL Server 2000 supports per field collation. So all you have to do is to
set the collation to 'case insensitive' for that field.


"WooGor" <zhang5gor@yahoo.com> wrote:
>
>When I installed my SQL Server 2000, I was asked to set Case Sensitive.
Now,
>I got a problem as follow.
>
>When I query the Student name list, my SQL Statement would be:
>
>Sql = “SELECTE * FROM T_Student Where StudentName LIKE '%" & strSearchName
>& "%'"
>
>When the user type john or JOHN in the box, namely, strSearchName, it returns
>nothing; however, if it is John, it then returns the records that state
in
>the database.
>
>Question to you: How could I get john or JOHN as the same as it is John,
>meaning that turn case sensitive to case insensitive for searching?
>
>Please note that the field StudenName is saved to the database as LastName,FirstName.
>For example, Smith,John.
>
>Any prompt response to my question is greatly appreciated.