Solved

# Order VARCHAR as Integer

Posted on 2001-07-12
1,076 Views
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
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

LVL 6

Accepted Solution

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

ID: 6276738
Alternatively you can use:

(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

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

LVL 1

Expert Comment

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;
EXCEPTION
WHEN OTHERS THEN RETURN 0;
END;
/
0

LVL 1

Expert Comment

ID: 6279735
And then try query

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

LVL 2

Expert Comment

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

LVL 4

Expert Comment

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

(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

ID: 6293564
LPAD solution dose not work with decimals

try this

(
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

0

LVL 1

Expert Comment

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

## Featured Post

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â€¦
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.
###### Suggested Courses
Course of the Month6 days, 23 hours left to enroll