?
Solved

case insensitive search

Posted on 2000-04-11
4
Medium Priority
?
529 Views
Last Modified: 2012-05-04
Hi,

I need to search through various fields of a table case insensitively (Informix server), something like:

select * from table1 where a1 like "%foo%"

any ideas how to tell informix to do this without looking at the case and without having to extend my query in the form
where a1 like "%foo%" or a1 like "%Foo%"...?
0
Comment
Question by:mafweb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 9

Expert Comment

by:david_levine
ID: 2703804
You can do 2 things. The appropraite solution would be based on the frequency and size of the table.

The simplest is to convert both to upper case:

select * from table1 where upper(a1) like "%FOO%"

This will cause the entire table to be scanned and an index not to be used. If your table is large or the number of searches is large, then you should create an additional column in your table that contains the upper case of the field you want to search (upper_a1 in your example). If you create an index on your column, you have a better chance (no quarantees though) that the index might be used, though a like with a leading and trailing % is never good (efficient).

You code would then look more like:

select * from table1 where upper_of_a1 like "%FOO%"

Make sense?

David
0
 
LVL 1

Author Comment

by:mafweb
ID: 2703831
Hi David,

thanks for the fast response, but it looks like informix does not have an upper() function :(
Also, your second idea sounds good, but it would consume too much space.

thx

maf
0
 
LVL 9

Expert Comment

by:david_levine
ID: 2703863
I found the following on Deja news. Hope it helps.

If you have 7.3x or later you can use the upper or lower function and store names as all lower case or upper case, thus:
 
select * from customers where last_name_upper = UPPER( :input );
 
This will even use an index on last_name_upper.  If you do not want to store the upper case version of the name you can use upper on both the input and the column but it will not use indexes:
 
select * from customers where UPPER( last_name ) = UPPER( "input );
 
If you do not have 7.30+ there is a stored procedure version of UPPER() in the IIUG Repository but it is SLOW or you could store upper and convert the input to upper case in code.
0
 
LVL 3

Accepted Solution

by:
oavidov earned 150 total points
ID: 2711618
mafweb ,
Usage at functional SQL word make miracle. Example is enclosed by your sample accordingly.
Select *
From table1
Where a1 like "%foo%" AND like "%Foo%"
Last sentence could be
Where a1="%foo%" AND a1="%Foo%"
Regads
oavidov



0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question