Search For

CarlosinFL · #1 (**permalink**) 08-30-11, 18:42

Can you please show or help me how I can do the following search but make it case insensitive? I did a Google search and found the LIKE condition however I can't find an example as shown below.

Code:

SELECT * FROM dp_user WHERE user_name = 'Carlos';
Empty set (0.00 sec)

I want to be able to find either 'Carlos' or 'carlos' in my SQL statement but I can't find anything on Google that shows me how to correct my SQL statement...

r937 · #2 (**permalink**) 08-30-11, 19:13

which database system are you using?

you posted in the ANSI SQL forum -- ANSI SQL is the "standard sql" language

often, ANSI SQL is not suficient to solve specific issues, such as case sensitivity

JarlH · #3 (**permalink**) 08-31-11, 02:31

Either use the LOWER function, or compare using a case-insensitive collation.

JarlH · #4 (**permalink**) 08-31-11, 02:32

[doube-post]

CarlosinFL · #5 (**permalink**) 08-31-11, 08:45

Quote:

Originally Posted by r937

which database system are you using?

you posted in the ANSI SQL forum -- ANSI SQL is the "standard sql" language

often, ANSI SQL is not suficient to solve specific issues, such as case sensitivity

I'm using PostgreSQL but assumed it was just something I could use ANSI SQL for.

JarlH · #6 (**permalink**) 08-31-11, 08:57

Have you tried

...where lower(user_name) = 'carlos'...?

Or ...where user_name collate english_ci_ai = 'Carlos'...?

I don't know any PostgreSQL's collation names, just wrote english_ci_ai as an example.

CarlosinFL · #7 (**permalink**) 08-31-11, 09:26

Quote:

Originally Posted by JarlH

Have you tried

...where lower(user_name) = 'carlos'...?

Or ...where user_name collate english_ci_ai = 'Carlos'...?

I just tried the following and looks like it worked:

Code:

ide=# SELECT id, fname, lname, email, dob, title FROM users WHERE LOWER(fname) = 'carlos';

 id | fname  |  lname  |        email        |    dob     |         title          
----+--------+---------+---------------------+------------+------------------------
  1 | Carlos | Mennens | carlos@iamghost.org | 1979-05-25 | Database Administrator
(1 row)

r937 · #8 (**permalink**) 08-31-11, 09:33

see also FAQ - PostgreSQL wiki ... 6.9 How do I perform regular expression searches and case-insensitive regular expression searches? How do I use an index for case-insensitive searches?

CarlosinFL · #9 (**permalink**) 08-31-11, 09:53

Now suppose I was trying to find a list of 'titles' which all contained the word 'administrator' in any case? I tried using the '%' to fill the gaps but it didn't work.

Code:

ide=# SELECT title FROM users;
          title           
--------------------------
 Database Administrator
 Linux Administrator

So I tried the following and I'm obviously missing something:

Code:

ide=# SELECT title FROM users WHERE LOWER(title) = '%administrator';
 title 
-------
(0 rows)

Sorry for my ignorance or stupidity but I'm just trying to learn and document as much as I can learn.

CarlosinFL · #10 (**permalink**) 08-31-11, 09:55

Quote:

Originally Posted by r937

see also FAQ - PostgreSQL wiki ... 6.9 How do I perform regular expression searches and case-insensitive regular expression searches? How do I use an index for case-insensitive searches?

So basically what this tells me is what I learn from that URL can't be copied over to database engines like MySQL or Oracle, correct?

r937 · #11 (**permalink**) 08-31-11, 12:35

Quote:

Originally Posted by CarlosinFL

...WHERE LOWER(title) = '%administrator';

you're thinking of LIKE here, not equals

r937 · #12 (**permalink**) 08-31-11, 12:36

Quote:

Originally Posted by CarlosinFL

So basically what this tells me is what I learn from that URL can't be copied over to database engines like MySQL or Oracle, correct?

absoposilutely, yes, it cannot

which is why questions asked in the ANSI SQL forum rarely get the final solution in actual ANSI SQL code

CarlosinFL · #13 (**permalink**) 08-31-11, 12:59

Quote:

Originally Posted by r937

which is why questions asked in the ANSI SQL forum rarely get the final solution in actual ANSI SQL code

That's very interesting!

Internet Services

Web Development

Digital Marketing

Consumer Tech