Sorting numeric values in the character field.

[vikasb2]

Hi All,

In a character field both numeric and Character values are being stored.

Select <field> from test order by <field> gives Output like this
1
10
2
3
A etc...

Is there any functions avaliable to get output like
1
2
3
10
11
A etc.,

More over If I use PADL() in the query then A is coming after 3.


Vikas Burman
Product Lead
coVeda technologies pvt ltd., Chandigarh

http://www.coveda.com

 

[ramani]

Hi

Example code

CREATE CURSOR test (field1 C(10))
INSERT INTO test (field1) VALUES ('1')
INSERT INTO test (field1) VALUES ('10')
INSERT INTO test (field1) VALUES ('11')
INSERT INTO test (field1) VALUES ('2')
INSERT INTO test (field1) VALUES ('23')
INSERT INTO test (field1) VALUES ('3')
INSERT INTO test (field1) VALUES ('4')
INSERT INTO test (field1) VALUES ('C')
INSERT INTO test (field1) VALUES ('ABC')

SELECT IIF(ISDIGIT(field1), ;
       PADL(ALLTRIM(field1),10,' '), ;
       field1) AS NewField1 FROM test ORDER BY 1

____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[vikasb2]

Thanks,

I used the following query
SELECT ;
IIF(len(chrtran(alltrim(field1), "1234567890", "")) = 0, ;
PADL(ALLTRIM(field1),10,' '), ;
number) AS NewField1 FROM test ORDER BY 1

because 1A comes after 9.





Vikas Burman
Product Lead
coVeda technologies pvt ltd., Chandigarh

http://www.coveda.com