Case insensitive SQL search with LIKE?

Case insensitive SQL search with LIKE?

Post by mr.. » Wed, 01 Sep 1999 04:00:00



I'm guessing this is a really common question, but my book is unable to
answer it.

I want to do a search like:

select * from dbname where name like "%joe%"

But what that gives me is "joe" and "joeseph". What I want is a case
insensitive version, so that I can get "Joe", or "joe", or "Joeseph",
or "McJoESePh" or anything.

How do I do that?

thanks,
Mike

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

 
 
 

Case insensitive SQL search with LIKE?

Post by Steve Koters » Wed, 01 Sep 1999 04:00:00



>I'm guessing this is a really common question, but my book is unable to
>answer it.

>I want to do a search like:

>select * from dbname where name like "%joe%"

>But what that gives me is "joe" and "joeseph". What I want is a case
>insensitive version, so that I can get "Joe", or "joe", or "Joeseph",
>or "McJoESePh" or anything.

One common approach is to force the values on both sides of the comparison
operator or predicate to be all in the same case, all in upper case for
instance. For CHAR literals, you simply enter the characters in upper case.
For columns or parameters, you can use the UPPER function. For example:

  SELECT *
  FROM dbname
  WHERE UPPER(name) LIKE "%JOE%"

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski              "Health nuts are going to feel stupid someday,
Felton, CA                  lying in hospitals dying of nothing."
                                                              -- Redd Foxx

 
 
 

1. case insensitive searches against case sensistive fields

                                                        Hello !

    I am trying to implement case insensitive search against fields that are
case sensitive.I found efficient way of performing exact match using
CHECKSUM function.The idea is to store additional field that stores CHECKSUM
of case insensitive representation of the field I would like to search
against. Then I build index on this field.This works pretty well using index
build on additional field.
    I am having problem implementing 'SearchCriteria%' or 'Start With'
searches.In  this case CHECKSUM is useless.I could use COLLATE clause to
convert to case insensitive collation but in this case no index could be
used which will be extremely slow with big tables.
    Is there efficient way to implement 'Start with' case insensitive
searches against case sensitive columns ?

                                    Thanks you in advance,

Igor.

2. Milliseconds

3. How to use both case sensitive and case insensitive search

4. Creating Aray lists

5. mixed case (case-insensitive) searching with FoxPro ODBC MS drivers

6. WHERE...

7. case-insensitive search with SQL in Oracle

8. Update Table A on the basis of Table B

9. case insensitive SQL searches

10. VB Datacontrol SQL for case insensitive string search

11. case-sensitive or case-insensitive: which is better in SQL 7

12. SQL Server case sensitive or case insensitive.