Solved

Order VARCHAR as Integer

Posted on 2001-07-12
9
1,076 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

732 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