# Order VARCHAR as Integer

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?
Asked:
###### Who is Participating?

Commented:
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

MI and AutomationCommented:
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

Commented:
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

Commented:
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

Commented:
And then try query

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

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

MI and AutomationCommented:
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

Commented:
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

Commented:
Sorry, the question don't request this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Already a member? Login.

All Courses

From novice to tech pro — start learning today.