Home » SQL & PL/SQL » SQL & PL/SQL » Case insensitive query (merged)
Case insensitive query (merged) [message #206885] Fri, 01 December 2006 23:22 Go to next message
deep_gh1
Messages: 13
Registered: October 2006
Location: India
Junior Member

Hi,

I have a table which contains records in uppercase and lowercase for the supplier name field.So at the time of select command I have to specify the proper case....like..

SQL> select supcode,supname from supplier
where supname = 'SAM';

But the above query fails if I write the query like below..

SQL> select supcode,supname from supplier
where supname = 'sam';

So, Is it possible to get out of this case sensitiveness ???...
i.e. I wanted to know if in the supname field the name entry is like in SAM(or whatever case) the query should ignore the case in where condition...!!!!

Any kind contribution will be highly obliged.

Thanks & Regards,

Randeep
Re: CASE_SENSITIVE_RECORD???? [message #206886 is a reply to message #206885] Fri, 01 December 2006 23:38 Go to previous messageGo to next message
Admin@Lightsql.Com
Messages: 36
Registered: November 2006
Member

if you change this query, you will get it.

select supcode,supname from supplier where lower(supname) = 'sam';

or

select supcode,supname from supplier where Upper(supname) = 'SAM';

This will work for sure.

However, there may be performance issues related to the above selects unless you tune and create function based index or the Upper(supname) or lower(supname) functions you use.

If you do not mind performance, use this at will.

Jay
Fun Programming with Oracle & Visual C++, Author of http://www.lightsql.com.


[Updated on: Fri, 01 December 2006 23:38]

Report message to a moderator

Re: CASE_SENSITIVE_RECORD???? [message #206887 is a reply to message #206886] Fri, 01 December 2006 23:55 Go to previous messageGo to next message
deep_gh1
Messages: 13
Registered: October 2006
Location: India
Junior Member

If I use like..

SQL> select supname from supplier
where LOWER(supname) = 'sam';

then the above query fails if the entry was in supplier field
SAM in supplier table.

If I use UPPER in that case then what about the entry as sam in supplier table....

So, what I try to tell is the query should ignore whatever case the table field contains...How it is possible???
CASE INSENSITIVE SEARCH IN SP??? [message #206889 is a reply to message #206885] Sat, 02 December 2006 00:31 Go to previous messageGo to next message
deep_gh1
Messages: 13
Registered: October 2006
Location: India
Junior Member

Hi all,

Actually I would like to search in "Case Insensitive" manner...There is a name field in my table i.e. suppname..so

SQL> select suppname from supplier;

o/p like..

SUPPNAME
-----------
Sam
sam
SAM
SaM
SAM

so if I restrict either of UPPER(suppname) or LOWER(suppname) in my sql query into my procedure...both them fails....because the entries are in mixed case....!!!

So, IS there any way to make the search query in stored procedure
so that search is "Not Case Sensitive"...i.e. the search query in stored procedure should ignore the case...i.e. the query does take care about whatever the case for the string in suppname field....The query should search in "Case Insensitive Manner" from the front end to which my stored procedure is running in the back end...!!!!!!

Any kind contribution will be obliged!!!!....

Thanks & Regards,

Randeep
Re: CASE INSENSITIVE SEARCH IN SP??? [message #206892 is a reply to message #206889] Sat, 02 December 2006 00:56 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Duplicate Post.
Re: CASE_SENSITIVE_RECORD???? [message #206897 is a reply to message #206887] Sat, 02 December 2006 01:28 Go to previous messageGo to next message
Admin@Lightsql.Com
Messages: 36
Registered: November 2006
Member

It should not fail. Did you try?

Can you give the exact query and results?

when you do like this

select supname from supplier where LOWER(supname) = 'sam';

The system coverts all the supnames into lower case supname for comparison. Hence, it must suceed.

For example, supname.row1='SAM'
supname.row2='Sam'
supname.row3='sAm'
supname.row4='sam'

all these rows get hit and will be listed.

Similarly for upper case.

Can you try a sample and try uploading to this if it is not working?

Jay
Fun Programming with Oracle & Visual C++, Author of http://www.lightsql.com.
Re: CASE_SENSITIVE_RECORD???? [message #206901 is a reply to message #206887] Sat, 02 December 2006 02:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you get your 'literal' string from another source and not as a literal as you showed us, you can use the lower or upper function on that side as well:
lower(supname) = lower(input)
Re: Case insensitive query (merged) [message #207187 is a reply to message #206885] Mon, 04 December 2006 09:23 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
It does not matter whether you use a literal string ('sam') or a variable. Force both sides to either upper or lower and you will get the results you're looking for. For example...

select supcode,supname from supplier 
where upper(supname) = upper('SAM');


Another recommendation would be to force a case (upper or lower) when the record is entered into the table. Do this with a trigger.

if INSERT then
  :new.supname = upper(:new.supname)
end if;


HTH,
Ron
Re: Case insensitive query (merged) [message #207315 is a reply to message #207187] Tue, 05 December 2006 00:50 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you could consider something like this: I've created a table which has mishmash of upper and lower case. Then, I've altered a session in order to make my queries case insensitive.
SQL> select * from brisime;

COL
--------------------
Little Foot
LITTLE foot
little FOOT

SQL> alter session set nls_comp = ANSI;

Session altered.

SQL> alter session set nls_sort = GENERIC_BASELETTER;

Session altered.

SQL> select * from brisime where col = 'little foot';

COL
--------------------
Little Foot
LITTLE foot
little FOOT

As long as it will be fine for a session, it wouldn't work on the entire system. It is not recommended to enable this by default, but rather write the ON LOGON database trigger which would then distinguish users which have to have this option enabled from the others.

Note that this won't work on all Oracle versions; I'm quite *sure* that it is supported from 9i onwards (being too lazy to search for it).


This is a copy of my own post at dBforums (being too lazy for the second time today)
Re: Case insensitive query (merged) [message #207427 is a reply to message #206885] Tue, 05 December 2006 09:06 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
This is a REALLY BAD idea. Setting your session defaults to force the session to ignore case might be appropriate while you're trying to pull some information, but not if you're developing procedures. IMHO, if you want something in a specific case, write your code to assure that it will be. Never assume that the data is in a specific format, because odds are there will be an exception somewhere down the line.

Besides, somewhere in the future you're going to find that something that is working fine for you is failing miserably for your supervisor who needs the report for the meeting in the next 3 minutes.

HTH,
Ron
Re: Case insensitive query (merged) [message #207468 is a reply to message #207427] Tue, 05 December 2006 12:17 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I never said it was an extraordinary good idea, but just another way to achieve OP's goal: case insensitive query.
Previous Topic: Trigger calls Java Class
Next Topic: How to get total of All the columns
Goto Forum:
  


Current Time: Sun Dec 04 08:54:05 CST 2016

Total time taken to generate the page: 0.14169 seconds