Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

case insensitive search in varchar fields

CUBRID Official Forum » General Discussions » case insensitive search in varchar fields

author
message
[Level:0]dragosh_r0ck

Post subject: case insensitive search in varchar fields

registered: 09/08/2012

IP: *.96.215.239

views: 2

Hi,

I have the following table:

CREATE TABLE "users"(
"user_id" integer AUTO_INCREMENT,
"user_name" character varying(4096) NOT NULL UNIQUE,
"user_email" character varying(4096) NOT NULL UNIQUE,
"user_nickname" character varying(4096) NOT NULL,
"user_firstname" character varying(4096) NOT NULL,
"user_lastname" character varying(4096) NOT NULL,
"user_points" integer DEFAULT 0,
"user_rank" integer DEFAULT 0,
CONSTRAINT pk_user_user_id PRIMARY KEY("user_id")
);


I want to be able to search users like this:

SELECT
    users.user_name AS username,
    users.user_nickname AS nickname
FROM
    users
WHERE
    users.user_name LIKE 'Joh%' OR users.user_nickname LIKE 'Joh%'


Questions:

1. Is there a better way to do this?

2. How can make this operation case insensitive? If I write 'joh%' I get no result.


Thanks,
Dragos

Quote
[Level:3]ovidiuv

# Post subject:Re: case insensitive search in varchar fields

profile

registered: 10/11/2011

IP: *.181.249.254

Hi Dragosh,

A solution would be to use the LOWER function:

SELECT

    users.user_name AS username,

    users.user_nickname AS nickname

FROM

    users

WHERE

    LOWER(users.user_name) LIKE LOWER('Joh%') OR LOWER(users.user_nickname) LIKE LOWER('Joh%')


Of course, if you give the value in lowercase (such as 'joh%'), then it's enough to run:


SELECT

    users.user_name AS username,

    users.user_nickname AS nickname

FROM

    users

WHERE

    LOWER(users.user_name) LIKE 'joh%' OR LOWER(users.user_nickname) LIKE 'joh%'


Regards,

Ovidiu

Quote
[Level:3]Esen Sagynov

# Post subject:Re: case insensitive search in varchar fields

profile

registered: 05/31/2011

IP: *.91.139.66

For your reference, there is also a STRCMP SQL function which compares two strings case-insentively. It returns 0 if strings are same. Not suitable for your case, since it requires two exact strings unlike 'Joh%'. But can be useful to know.

Quote
[Level:0]dragosh_r0ck

# Post subject:Re: case insensitive search in varchar fields

profile

registered: 09/08/2012

IP: *.96.215.239

Ovidiu,

Thanks, LOWER function solved it. Do you think that something like below it will be faster:

SELECT
    users.user_name AS username,
    users.user_nickname AS nickname
FROM
    users
WHERE
    LOWER(CONCAT_WS(' ', users.user_name, users.user_nickname)) LIKE LOWER('%Joh%')

Of course, in this case I have to use % also in the front of the query.

Quote
[Level:3]Esen Sagynov

# Post subject:Re: case insensitive search in varchar fields

profile

registered: 05/31/2011

IP: *.91.139.66

Just theoretically I think the first one may perform better on large data set.

  1. In the second one you additionally perform string concatenation operation which is relatively more expensive than searching within a string.
  2. In the first case, if the first condition is true, the engine will not need to evaluate the second condition.

This is just theoretically, for real answer it's necessary to perform tests on large data set.

Also, don't forget to create two indexes for user_name and user_nickname. Considering the nature of user names and nicknames, they aren't too long to result in large index files. This will ensure the engine doesn't touch the disk to retrieve these values, considering all indexes are in the memory. Both values would be stored in the index tree.

Quote
[Level:0]dragosh_r0ck

# Post subject:Re: case insensitive search in varchar fields

profile

registered: 09/08/2012

IP: *.96.215.239

Esen,

Thank you very much for your answer. For user_name I have an index, I will consider adding one also for the user_nickname field.

Quote
[Level:3]Esen Sagynov

# Post subject:Re: case insensitive search in varchar fields

profile

registered: 05/31/2011

IP: *.91.139.66

Just for your reference, you may also like to look at these index tuning techniques to learn more about different possibilities.

  1. CUBRID Query Tuning Techniques
  2. CUBRID 8.4.0 Key Features

Quote




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: