Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

How to sort varchar columns

Codes here !

Moderators: macek, egami, gesf

How to sort varchar columns

Postby lberry » Tue Oct 30, 2012 11:14 am

I am trying to create a database that has a list of land lots. The values are typically "2A" or "10.0A" (where A is acres). The user enters the min and/or the max value of what they are looking for. For example, I might want a lot greater than 2A and less than 10A. For the most part the code works, if I do a min of 2A I get entries greater than 2. However, if I do anything above 9, it gets weird, returning 5A and 2A, even if I use "10.0A".

Here is my SQL statement:

select street_number,street_name,city,zip from sf_records2 where 1 and site_area>='10.0A' and
site_area != 'IRR' and site_area REGEXP 'A$' order by street_name,street_number limit 0,250;

Returns (for example):

1A
2A
17.45A
8.98A
10.01A

If I use '9.1A', it works (for the most part). Example returns:

9.62A
9.8A
9.2A

And just for fun, '15A'

3.04A
2A

So anything '10' and above is just weird, decimal point or not.

Notes: 1. The RegExp 'A$' is because there are also values in there for 'SqFt'.
2. If I were generating the data, I would split the A off before it went in the database or,
ideally remove it altogether, but I am stuck with data from several sources that come in this
way.

Any ideas or thoughts are appreciated.
Thanks
lberry
New php-forum User
New php-forum User
 
Posts: 5
Joined: Wed May 16, 2012 3:20 pm

Return to mySQL & php coding

Who is online

Users browsing this forum: Majestic-12 [Bot] and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.