Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Tue Nov 13, 2018 1:21 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: mySQL Parse/concatenate functions?
PostPosted: Mon Mar 11, 2013 12:15 pm 
Offline

Joined: Sun Jan 28, 2007 2:38 pm
Posts: 231
I have a legacy mySQL Database with a Column in it called limit_liability

The values look like this

$1,000,000/$3,000,000
$200,000/$600,000
$1,000,000/$1,000,000
$500,000/$1,000,000
$500,000/$1,500,000

I need to use some sort of mySQL PARSE function to ORDER a sort by The First Number then the second number So that the results are

$200,000/$600,000
$500,000/$1,000,000
$500,000/$1,500,000
$1,000,000/$1,000,000
$1,000,000/$3,000,000


Something like:

SELECT DISTINCT limit_liability from myTable ORDER BY (HELP!)


Top
 Profile  
Reply with quote  
 Post subject: Re: mySQL Parse/concatenate functions?
PostPosted: Tue Mar 12, 2013 11:23 am 
Offline

Joined: Fri Oct 13, 2006 3:18 pm
Posts: 327
You can go:

SELECT DISTINCT limit_liability from myTable ORDER BY limit_liability

If you don't add anything on the end, it will automatically sort in ascending order.
For decending order, add DESC on the end.

Hope this helps.

Regards


Top
 Profile  
Reply with quote  
 Post subject: Re: mySQL Parse/concatenate functions?
PostPosted: Wed Mar 13, 2013 8:26 am 
Offline

Joined: Sun Jan 28, 2007 2:38 pm
Posts: 231
azrael2000 wrote:
You can go:

SELECT DISTINCT limit_liability from myTable ORDER BY limit_liability

s


Unfortunately that results in as it is an Alpha Sort:


$1,000,000/$1,000,000
$1,000,000/$3,000,000
$200,000/$600,000
$500,000/$1,000,000
$500,000/$1,500,000


Top
 Profile  
Reply with quote  
 Post subject: Re: mySQL Parse/concatenate functions?
PostPosted: Wed Mar 13, 2013 8:33 am 
Offline

Joined: Wed May 20, 2009 4:43 pm
Posts: 958
I haven't tried it, but how about something like this:

Quote:
SELECT
SUBSTRING_INDEX(limit_liability, '/', 1) AS liability1,
SUBSTRING_INDEX(limit_liability, '/', -1) AS liability2
FROM myTable
ORDER BY liability1, liability2

_________________
RB 2009r4 Windows XP


Top
 Profile  
Reply with quote  
 Post subject: Re: mySQL Parse/concatenate functions?
PostPosted: Wed Mar 13, 2013 11:55 am 
Offline

Joined: Wed May 20, 2009 4:43 pm
Posts: 958
This should work, although there is probably a more efficient way to convert the values to numerics.

Quote:
SELECT
limit_liability,
CONVERT(SUBSTRING_INDEX(REPLACE(REPLACE(limit_liability , ',', ''), '$', ''), '/', 1), signed) AS liability1,
CONVERT(SUBSTRING_INDEX(REPLACE(REPLACE(limit_liability , ',', ''), '$', ''), '/', -1), signed) AS liability2
FROM myTable
ORDER BY liability1, liability2

_________________
RB 2009r4 Windows XP


Top
 Profile  
Reply with quote  
 Post subject: Re: mySQL Parse/concatenate functions?
PostPosted: Wed Mar 13, 2013 3:15 pm 
Offline

Joined: Sun Jan 28, 2007 2:38 pm
Posts: 231
Brilliant! Works just fine.


Top
 Profile  
Reply with quote  
 Post subject: Re: mySQL Parse/concatenate functions?
PostPosted: Thu Mar 14, 2013 10:24 pm 
Offline

Joined: Sat Dec 04, 2010 9:14 pm
Posts: 918
Not to hijack this thread, but is that the same solution to ordering when values do not order properly?

1000
2
2000
30
400


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group