Case Insensitive Query 
Author Message
 Case Insensitive Query

Hi,

How can I make a case insensitive query?

select * from mybase.dbf
where object like "%white%"

This query only returns the lower case letters, what can I do to include
everything, regardeless of case?

Thanks in advance



Sat, 24 Mar 2001 03:00:00 GMT  
 Case Insensitive Query

Quote:

>How can I make a case insensitive query?

>select * from mybase.dbf
>where object like "%white%"

>This query only returns the lower case letters, what can I do to include
>everything, regardeless of case?

In standard SQL (not just in the BDE's local SQL), string comparisons like
this are by design case-sensitive. You can override this default behavior
by forcing both comparison values to be all in the same casing. For string
literals, this can be a matter of either using a function like UPPER or
just putting the value all in uppercase. For columns and parameters, use
the UPPER function.

  SELECT *
  FROM mybase.dbf
  WHERE (UPPER(object) like "%WHITE%")

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "The knowledge of the world is only to
Technical Publications              be acquired in the world, and not in a
INPRISE Corporation                 closet."
http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)



Sat, 24 Mar 2001 03:00:00 GMT  
 Case Insensitive Query


Quote:
Koterski) writes:
> For string
>literals, this can be a matter of either using a function like UPPER or
>just putting the value all in uppercase. For columns and parameters, use
>the UPPER function.

Bear in mind that the use of the Upper function means that if there is an index
on that column, then it cannot be used, which will have performance
implications. You might like to experiment with your RDBMS and try, as Steve
suggests, to use all caps for your parameter.

Charles Johnson



Sun, 25 Mar 2001 03:00:00 GMT  
 Case Insensitive Query


Quote:
Koterski) writes:
> For string
>literals, this can be a matter of either using a function like UPPER or
>just putting the value all in uppercase. For columns and parameters, use
>the UPPER function.

Bear in mind that the use of the Upper function means that if there is an index
on that column, then it cannot be used, which will have performance
implications. You might like to experiment with your RDBMS and try, as Steve
suggests, to use all caps for your parameter.

Charles Johnson



Sun, 25 Mar 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. How to perform a case-insensitive query?

2. Case insensitive queries in D3

3. Case-insensitive queries

4. How to make InterBAse query case insensitive?

5. How to make SQL query case insensitive?

6. Case insensitive SQL query

7. Help - case-insensitive search?

8. InterBase case-INsensitive index

9. CASE INSENSITIVE Boyer Moore

10. Using case insensitive SQL strings in delphi 2.0 C/S

11. Case Insensitive Match using SQL- How to?

12. Sort Stringfields case insensitive - how?

 

 
Powered by phpBB® Forum Software