Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Order VARCHAR as Integer

Posted on 2001-07-12
9
Medium Priority
?
1,096 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 200 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

569 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