Strings and order by

[Cecilio Peral]

Hi

I have a table with only a field

create table t
(text varchar2(100));

I put 4 rows in it

insert into t (text) values ('BA000');
insert into t (text) values ('BA001');
insert into t (text) values ('Ba000');
insert into t (text) values ('Ba001');

If I query the table order by text I get the following results:

SQL> select * from t order by text;

TEXT
-----
BA000
Ba000
BA001
Ba001

SQL>

It sounds strange to me getting the results case insensitive, I thougth
should return all the records 'BA%' and then 'Ba%'.

Am I wrong?

Is there any way to get the results in this way?

Thanks in advance for any clue you can give me.

Regards

Cecilio

[FredBear]

"Cecilio Peral" <removethis_cecilio@tangerine.es> a écrit dans le message de news: dh0d65$it6$1@news.ya.com...
| Hi
|
| I have a table with only a field
|
| create table t
| (text varchar2(100));
|
| I put 4 rows in it
|
| insert into t (text) values ('BA000');
| insert into t (text) values ('BA001');
| insert into t (text) values ('Ba000');
| insert into t (text) values ('Ba001');
|
| If I query the table order by text I get the following results:
|
| SQL> select * from t order by text;
|
| TEXT
| -----
| BA000
| Ba000
| BA001
| Ba001
|
| SQL>
|
| It sounds strange to me getting the results case insensitive, I thougth
| should return all the records 'BA%' and then 'Ba%'.
|
| Am I wrong?
|
| Is there any way to get the results in this way?
|
| Thanks in advance for any clue you can give me.
|
| Regards
|
| Cecilio
|
|

As per "Sorting Query Results" section of SQL Reference:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/queries6.htm#2054000

<quote>
The mechanism by which Oracle sorts values for the ORDER BY clause is specified either explicitly by the NLS_SORT initialization
parameter or implicitly by the NLS_LANGUAGE initialization parameter. You can change the sort mechanism dynamically from one
linguistic sort sequence to another using the ALTER SESSION statement. You can also specify a specific sort sequence for a single
query by using the NLSSORT function with the NLS_SORT parameter in the ORDER BY clause.
</quote>

For NLSSORT see:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions82a.htm#78401

Regards
Michel Cadot