Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1107
  • Last Modified:

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?
0
ianlesoft
Asked:
ianlesoft
1 Solution
 
M-AliCommented:
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
 
jtriftsMI 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
 
r_sudipCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
alx512Commented:
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
 
alx512Commented:
And then try query

SELECT * FROM table_name
ORDER BY SALARY.TO_NUMBER_EX(field_name);
0
 
RMZCommented:
SELECT * FROM table_name
ORDER BY TO_NUMBER(field_name);
-RMZ-
0
 
jtriftsMI 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
 
alx512Commented:
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
 
alx512Commented:
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now