Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Order VARCHAR as Integer

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

 
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

604 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