mattclements Posted September 16, 2009 Share Posted September 16, 2009 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 Quote Link to comment Share on other sites More sharing options...
Zane Posted September 16, 2009 Share Posted September 16, 2009 what is the code you are using to sort is now Quote Link to comment Share on other sites More sharing options...
artacus Posted September 16, 2009 Share Posted September 16, 2009 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. Quote Link to comment Share on other sites More sharing options...
mattclements Posted September 17, 2009 Author Share Posted September 17, 2009 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"; Quote Link to comment Share on other sites More sharing options...
artacus Posted September 28, 2009 Share Posted September 28, 2009 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.