Case sensitive search

Dear All I am using Informix 11.50 with PHP 4.3.9 when I try to search the any value in any column then it treat as case sensitive like select * from care_person where name_first like 'nierjesh%' select * from care_person where name_first like 'Nierjesh%' select * from care_person where name_first like 'Nierjesh%' all queries return different result .Is there any way so that I can avoid the case sensitive search. Regards- Nierjesh --0016363100f39ce7bc0482ee5fb4

select * from care_person where lower(name_first) like '$name_variable%' where you have used the php function to lowercase the name_variable Jonathon Wyza CX & CBORD System Administrator CX Programmer/Analyst Administrative Computing Bethel College (574)-257-3381 AIM: Iamwyza jonathon.wyza@bethelcollege.edu ============================== SLES 10x32 SP2 & IDS 10.00.UC9 " I would love to change the world, but they won't give me the source code." -- Unknown -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of nierjesh kumar Sent: Monday, March 29, 2010 6:57 AM To: ids@iiug.org Subject: Case sensitive search [19445] Dear All I am using Informix 11.50 with PHP 4.3.9 when I try to search the any value in any column then it treat as case sensitive like select * from care_person where name_first like 'nierjesh%' select * from care_person where name_first like 'Nierjesh%' select * from care_person where name_first like 'Nierjesh%' all queries return different result .Is there any way so that I can avoid the case sensitive search. Regards- Nierjesh --0016363100f39ce7bc0482ee5fb4 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

This is a FAQ. Striclty answering your question you could do: SELECT * FROM care_person WHERE UPPER(name_first) like UPPER('nierjesh%') This works, but if you have an index on name_first, it will not use it, so performance will suffer. To overcome this you can create a functional index. A functional index is created on the result of a function call. UPPER cannot be used in a function index because it hasn't a property needed for it (non variant if memory serves me well). But you can create a "myUPPER" wrapper function that inside calls UPPER. This issue was already discussed in the list. You can find it. If not we can give you an example. Regards On Mon, Mar 29, 2010 at 11:57 AM, nierjesh kumar <nierjeshkumar@gmail.com>wrote: > Dear All > > I am using Informix 11.50 with PHP 4.3.9 when I try to search the any value > in any column then it treat as case sensitive like > > select * from care_person where name_first like 'nierjesh%' > select * from care_person where name_first like 'Nierjesh%' > select * from care_person where name_first like 'Nierjesh%'> > all queries return different result .Is there any way so that I can avoid > the case sensitive search. > > Regards- > Nierjesh > > --0016363100f39ce7bc0482ee5fb4 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e65c7c20828c630482ef0826

LOWER() in SELECT will work, but my advise to you is to convert all your names and/or any CHAR columns to upper-case and put the UPSHIFT attribute in the forms field-tag so that when new names are entered or the field is queried, you wont have anymore problems with obtaining all the qualifying results. All my applications use UPSHIFT for CHAR columns so any entries made in lower-case are automatically converted to upper-case.

You can create a case insensitve function (like an upshift) and then create an index using the results of that function as the key. After that (and update statistics naturally) any query that filters using that function willwork. You can't use the built-in UPPER function because unfortunately it is not marked as INVARIANT (you can only build an index on the results of an INVARIANT function). But, you can write a little INVARIANT function that calls UPPER() and returns the result and index on that. Once that's done you would SELECT * from care_person where my_invariant_upper( name_first) like'NIERJESH%'; Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Mar 29, 2010 at 6:57 AM, nierjesh kumar <nierjeshkumar@gmail.com>wrote: > Dear All > > I am using Informix 11.50 with PHP 4.3.9 when I try to search the any value > in any column then it treat as case sensitive like > > select * from care_person where name_first like 'nierjesh%' > select * from care_person where name_first like 'Nierjesh%' > select * from care_person where name_first like 'Nierjesh%'> > all queries return different result .Is there any way so that I can avoid > the case sensitive search. > > Regards- > Nierjesh > > --0016363100f39ce7bc0482ee5fb4 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747b10424b6030482f2627e