SQL Statement: Case sensitive


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: SQL Statement: Case sensitive

  1. #1
    WooGor Guest

    SQL Statement: Case sensitive


    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.

  2. #2
    David Satz Guest

    Re: SQL Statement: Case sensitive

    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.




  3. #3
    Andre Guest

    Re: SQL Statement: Case sensitive



    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.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center