[thelist] [SQL] Sorting numerical and text data

Howard Cheng howcheng at ix.netcom.com
Thu Apr 11 17:28:00 CDT 2002


Solved it!

SELECT CAST(
     CASE WHEN ISNUMERIC(luggageCapacity) = 1
              THEN luggageCapacity
          ELSE null
     END
AS INT) AS luggageCapacity

At 03:02 PM 4/11/2002 -0700, you wrote:
>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'.

::::::::::::::::::::::
Howard Cheng
howcheng at ix.netcom.com
AIM: bennyphoebe
ICQ: 47319315




More information about the thelist mailing list