Home » SQL & PL/SQL » SQL & PL/SQL » How '>' behaves while doing string comparison? (Oracle 10g)
How '>' behaves while doing string comparison? [message #436931] Tue, 29 December 2009 10:58 Go to next message
sripy123
Messages: 2
Registered: December 2009
Junior Member
Hi,

i created a sample table with following records.

"select * from sample " returns below records

id Firstname lastname
--------------------------------
1 sri1 ram1
2 sri2 ram2
3 sri3 ram3
4 sri4 ram4
5 sri5 ram5
6 sri6 ram6

Question
--------

Below query returns all the records
select * from sample s where s.FIRSTNAME > all('srhabcdefghijklmnopqrstuvwxyz')

But this query doesn't return any record
select * from sample s where s.FIRSTNAME > all('sriabcdefghijklmnopqrstuvwxyz')

How '>' behaves when comparing strings?
Re: How '>' behaves while doing string comparison? [message #436934 is a reply to message #436931] Tue, 29 December 2009 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does " all('srhabcdefghijklmnopqrstuvwxyz')" means?

Quote:
But this query doesn't return any record

I trust you, and I bet it returns an error.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Use SQL*Plus and copy and paste your session.
Post a workingTest case: create table and insert statements along with the result you want with these data.

Regards
Michel

[Updated on: Tue, 29 December 2009 11:07]

Report message to a moderator

Re: How '>' behaves while doing string comparison? [message #436936 is a reply to message #436931] Tue, 29 December 2009 11:13 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Besides, the ALL operator compares the value in the condition to every value in a list or the values returned by a query.
Shocked
Re: How '>' behaves while doing string comparison? [message #436951 is a reply to message #436931] Tue, 29 December 2009 15:05 Go to previous messageGo to next message
sripy123
Messages: 2
Registered: December 2009
Junior Member
Ok, here is the complete test scenario.

create table sample(firstname varchar2(30),lastname varchar2(30));

commit;

insert into sample values('sri1','ram1');
insert into sample values('sri2','ram2');

commit;

Query : 1

select * from sample s where s.firstname > 'srh';

Returns all 2 records

Query : 2

select * from sample s where s.firstname > 'srhabcdefghijklmnopqrstuvwxyz';

Returns all 2 records. How this '>' operator behaves while doing the varchar2 comparison.

Query : 3

select * from sample s where s.firstname > 'srj';

No rows returns.


regards
sriram.
Re: How '>' behaves while doing string comparison? [message #436952 is a reply to message #436951] Tue, 29 December 2009 15:32 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
It returns anything "bigger" (alphabetically)

What exactly "bigger" means in the alphabetically sense can be altered with the NLS_SORT database parameter depending on what language sorting rules should be used.

Re: How '>' behaves while doing string comparison? [message #436953 is a reply to message #436951] Tue, 29 December 2009 15:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is the question?

Just for information, you don't have to commit a DDL like create table.

Regards
Michel
Re: How '>' behaves while doing string comparison? [message #436954 is a reply to message #436951] Tue, 29 December 2009 16:10 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
> in string behaves like with numbers less than 1 or if you want a more formal term, it is a lexicographic order.

Regards
Michel
Previous Topic: sql problem
Next Topic: How to add minutes to date?
Goto Forum:
  


Current Time: Wed Dec 07 18:24:26 CST 2016

Total time taken to generate the page: 0.22272 seconds