postal code ASC order problem

[jsltd]

Hi,
I have a drop down box which lists postcodes (UK address codes) in ASC order. This all works fine, except the order is not as I'd like:

e.g. The ASC listed postcodes displays like so:

KT1
KT10
KT11
KT12
KT16
KT18
KT2

I can see the list is ordered 'correctly' however I need it to more logically, list as follows:
KT1
KT2
KT10
KT11
KT12
KT16
KT18

Is this possible using SQL or any other method ?

[Newsgroup_User]

Rename your list to KT01, KT02

Other than that, you could use a ranking/order field.

HTH,

Jon

"jsltd" <webforumsuser@macromedia.com> wrote in message
news:ejt2id$4mc$1@forums.macromedia.com...
> Hi,
> I have a drop down box which lists postcodes (UK address codes) in ASC
> order.
> This all works fine, except the order is not as I'd like:
>
> e.g. The ASC listed postcodes displays like so:
>
> KT1
> KT10
> KT11
> KT12
> KT16
> KT18
> KT2
>
> I can see the list is ordered 'correctly' however I need it to more
> logically, list as follows:
> KT1
> KT2
> KT10
> KT11
> KT12
> KT16
> KT18
>
> Is this possible using SQL or any other method ?
>
>

[jsltd]

Hi Jon, Thanks for the ideas.
I can't change the codes in that way as they are postal codes and KT01 is not KT1, if you see what I mean.
I have thought of using a ranking method as you suggest, however I need to allow the client to manage the codes in the database, so I need them to be able to just tag a new code on the end (add a new record) and for it to list it 'correctly' in ASC.

[Newsgroup_User]

jsltd wrote:
> I can't change the codes in that way as they are postal codes and KT01 is not
> KT1, if you see what I mean.

UK postcodes are particularly difficult to handle in this way. If your
are using PHP, you could gather all results in a multidimensional array,
and then use natcasesort() to reorder the results according to postcode.

The alternative would be to use three columns instead of one for the
postcode. You might call them postcode1, which has just the initial
alphabetical characters; postcode2, with the number before the break;
and postcode3, with the rest of the code. There may be a better way.
Like you, I would be interested to hear of it.

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

[Newsgroup_User]

In SQL Server you could possibly have something like

select pc from t
order by left(pc,2),cast(right(pc,2) as int)

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

[jsltd]

Hi Jules,
Thanks for the input. I'm tried this out but not getting syntax corrrect. I have this so far:

"SELECT * FROM postcodes ORDER BY left(postcode_code,2), cast(right(postcode_code,2) as int)"

appreciate any further input/ideas ?

[jsltd]

Hi David, Thanks for the input. I'm using ASP.

The only way I can think is to add a '0' in front of single digit codes dynamically in the SQL statement before sorting them ASC. e.g changing KT1 into KT01 for the sake of sorting, not displaying.

Is this possible with SQL ? can anyone help or suggest a more effective alternative.

[Newsgroup_User]

jsltd wrote:
> Is this possible with SQL ? can anyone help or suggest a more effective
> alternative.

Looks like Jules has the answer for you. I must try it with MySQL
because there are similar functions that would probably do the same
trick. But for ASP, Jules is your man.

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

[jsltd]

I've looked at the ms documentation for CAST and have updated the SQL to this:

SELECT * FROM postcodes ORDER BY (left(postcode_code,2)), cast(right(postcode_code,2)) as int ASC

However, still getting a 'missing operator' error.

I'm using access as my database, does this make any difference ?

[jsltd]

I didn't realise there where different flavours of sql depending on the database for a start !

I'm using Access 2003. So I discovered the equiv function for this is CINT. However I'm struggling to understand what its doing and how to do it ?

Here is my sql so far:

"SELECT * FROM postcodes ORDER BY (postcode_code,2), CINT(postcode_code)"

which I know is wrong, but I'm at a loss now how to apply CINT to my query.

I'd appreciate any help, thanks.

[Newsgroup_User]

Maybe

SELECT * FROM postcodes ORDER BY left(postcode_code,2),
CINT(right(postcode_code,2))

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

[jsltd · Accepted solution]

Hi Jules,
Thanks again for the input, really appreciated. I have found the solution:

SELECT * FROM postcodes ORDER BY Left(postcode_code,2), CInt(Mid(postcode_code,3,2))

This works a treat perfectly. Thanks again.