Solved

Order VARCHAR as Integer

Posted on 2001-07-12
9
1,074 Views
Last Modified: 2009-07-01
Hello,

I need to order field from 1 to 99, the field is VARCHAR

now with ORDER BY I can't order them cuse in string 9 is bigger then 10,

So how can I order them?
0
Comment
Question by:ianlesoft
9 Comments
 
LVL 6

Accepted Solution

by:
M-Ali earned 50 total points
ID: 6276385
You can convert the varchar field's output to number using TO_NUMBER function.

For example, your query would be something like this:

SELECT * FROM table_name
ORDER BY TO_NUMBER(field_name, 'FM99');


Ali
0
 
LVL 4

Expert Comment

by:jtrifts
ID: 6276738
Alternatively you can use:

ORDER BY LPAD(field_name,nn,'0')

(where nn is the column size)...the Left padding will put zeroes in front and then will sort as normal numerics).

This is also a better solution than using a to_number since if you have some non-numerics in that field, the sort will not fall over as it will with the to_number solution provided.

Hope this helps,

JT
0
 

Expert Comment

by:r_sudip
ID: 6278225
Try the following query

 Assuming the tablename is test and the column in question is col1

 Select  t.col1 from test t , (select max(length(col1)) len from test) order by lpad(col1,len);


Sudip :-)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:alx512
ID: 6279732
if field can have the non numeric values you can use my function:

CREATE OR REPLACE FUNCTION SALARY.TO_NUMBER_EX(NUM IN VARCHAR2) RETURN NUMBER IS
  I NUMBER;
  L NUMBER;
  PCOUNT NUMBER;
  ECOUNT NUMBER;
  SCOUNT NUMBER;
  ZCOUNT NUMBER;
  C CHAR(1);
  SRC VARCHAR2(250);
  RES VARCHAR2(250);
BEGIN
  RES := '';
  SRC := UPPER(RTRIM(LTRIM(NUM)));
  PCOUNT := 0;
  ECOUNT := 0;
  SCOUNT := 0;
  ZCOUNT := 0;
  L := LENGTH(SRC);
  FOR I IN 1..L LOOP
    C := SUBSTR(SRC, I, 1);
    IF C IN ('0','1','2','3','4','5','6','7','8','9','.','E','-','+') THEN
      IF C = '.' THEN
        PCOUNT := PCOUNT + 1;
        IF PCOUNT = 1 THEN
          RES := RES||C;
        END IF;
      ELSIF C = 'E' THEN
        ECOUNT := ECOUNT + 1;
        IF ECOUNT = 1 THEN
          RES := RES||C;
        END IF;
      ELSIF C IN ('-', '+') THEN
        IF I = 1 THEN
          SCOUNT := SCOUNT + 1;
          IF SCOUNT = 1 THEN
            RES := RES||C;
          END IF;
        ELSIF SUBSTR(SRC, I-1, 1) = 'E' THEN
          ZCOUNT := ZCOUNT + 1;
          IF ZCOUNT = 1 THEN
            RES := RES||C;
          END IF;
        ELSE
          PCOUNT := PCOUNT + 1;
          IF PCOUNT = 1 THEN
            RES := RES||'.';
          END IF;
        END IF;
      ELSE
        RES := RES||C;
      END IF;
    ELSE
      PCOUNT := PCOUNT + 1;
      IF PCOUNT = 1 THEN
        RES := RES||'.';
      END IF;
    END IF;
  END LOOP;
  RETURN TO_NUMBER(NVL(RES, 0));
  EXCEPTION
    WHEN OTHERS THEN RETURN 0;
END;
/
0
 
LVL 1

Expert Comment

by:alx512
ID: 6279735
And then try query

SELECT * FROM table_name
ORDER BY SALARY.TO_NUMBER_EX(field_name);
0
 
LVL 2

Expert Comment

by:RMZ
ID: 6293131
SELECT * FROM table_name
ORDER BY TO_NUMBER(field_name);
-RMZ-
0
 
LVL 4

Expert Comment

by:jtrifts
ID: 6293148
RMZ,

What will happen with your solution if a value in field_name is actually a character rather than a number?

I still think you'd be safer padding the value with zeroes and then sorting on that as it will work for both numerics stored in the VARCHAR as well as non-numerics.

SELECT * FROM table_name
ORDER BY LPAD(field_name,nn,'0');

(where nn is the column size)...the Left padding will put zeroes in front and then will sort as normal
numerics).

JT



0
 
LVL 1

Expert Comment

by:alx512
ID: 6293564
LPAD solution dose not work with decimals

try this

select LPAD(a.f, 10, '0') from
(
  select to_char(12.275) f from dual union all
  select to_char(1.265) f from dual union all
  select to_char(1.27) f from dual
) a
order by LPAD(a.f, 10, '0');
 
0
 
LVL 1

Expert Comment

by:alx512
ID: 6293578
Sorry, the question don't request this.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can i Import Access Table Into Oracle Using Toad 36 274
pl/sql - query very slow 26 87
Oracle collections 15 37
Checking for column width 8 38
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

756 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