[thelist] [SQL] Sorting numerical and text data

[Schoolcraft, Jeffrey]

> -----Original Message-----
> From: Howard Cheng [mailto:howcheng at ix.netcom.com]
> Sent: Thursday, April 11, 2002 6:03 PM
> To: thelist at lists.evolt.org
> Subject: [thelist] [SQL] Sorting numerical and text data
>
>
> Evolters,
>
> I have a conundrum wherein I have one VARCHAR column that
> contains both numerical and text data. I want to be able to
> sort my result set with that column, but the numbers have be
> treated as numbers, not as text.
>
> I other words, if I have a result set with the values 4, 28,
> 100, and 'variable' (this field indicates luggage capacity of
> an airplane), the results  should sort in that order, but
> because it's a VARCHAR field it becomes 100, 28, 4, 'variable'.
>
> Any ideas? Thanks.

You're getting stuck with lexigraphical order, so the sorting is correct.  I
know this might not be the place to ask, but why do you store two types of
data in the same field ?  Couldn't you split out the 'variable' into a
separate field.

I guess I'd need more of your schema to talk intelligently to that.

Jeff