Sort String Column as Integer

[mattclements]

Hey all,

Just a quick one (should be simple) - at the moment my database has a sorting function when using the screens.

However the Client Ref Number is a "String" fieldtype as the client can enter both numbers & letters.

 

However when sorting it shows the following:

Test

Testing

8044

8045

8046

22454

 

(Obviously sorting as a String rather than by numbers)

 

The to_number() in a sort throws an error as there are letters & symbols in the data.

 

Any ideas?

Regards,

Matt

[Zane]

what is the code you are using to sort is now

[artacus]

You'll have to write your own function that will strip out nonnumeric characters and return a number and null if there are no numbers in the string.

[mattclements]

Ok

My current code is:

  elseif ($sqlsort=="c.club_ref")
    $stmt="select * from (select c.contract_id,c.name,wc.EMAIL,wc.TELEPHONE,wc.MOBILE,c.our_ref,c.club_ref,to_char(c.sign_date,'DD-Mon-YYYY') sign_date,to_char(c.lastpay_date,'DD-Mon-YYYY') lastpay_date,c.lastpay_amt,c.stat_flag,row_number() over (ORDER BY c.club_ref desc) r from   web_members c, web_contracts wc where  c.login_id=:sucid and  c.club_id=:club_id and c.status = 'L' and c.contract_id = wc.contract_id) where r between :min_row and :max_row";

[artacus]

Right, and you'll have to write a user defined function that removes all non-numeric characters and returns a number. I'm not sure which database you are using, but if it has regex functions it should be fairly straight forward.