ORDER - varchar column contains Char & int values

Hi,
I have a table with a Varchar column(col1) contains values as below,
Low
Upper
4
Lp
31
1
2
Jack
3
default order/order by col1 is
1
2
3
31
4
Jack
Low
Lp
Upper
I want to order this column in such a way that
blank spaces first, numbers second(in ascending) and alphabet...
1
2
3
4
31
Jack
Low
Lp
Upper
Please advise.
Thanks,
SouraUSE tempdb
GO
CREATE TABLE a (cola varchar(10))
GO
INSERT INTO a VALUES('Low')
INSERT INTO a VALUES('Upper')
INSERT INTO a VALUES('4')
INSERT INTO a VALUES('Lp')
INSERT INTO a VALUES('31')
INSERT INTO a VALUES('1')
INSERT INTO a VALUES('2')
INSERT INTO a VALUES('Jack')
INSERT INTO a VALUES('3')
GO
SELECT cola,
CASE
WHEN ISNUMERIC(cola) = 1 THEN CAST(cola AS bigint)
ELSE 9223372036854775807 -- max bigint
END AS ordcol
FROM a
ORDER BY ordcol, cola
--
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:B7D8A70A-C130-4C6C-9CDF-B453380E810D@.microsoft.com...
> Hi,
> I have a table with a Varchar column(col1) contains values as below,
> Low
> Upper
> 4
> Lp
> 31
> 1
> 2
> Jack
> 3
> default order/order by col1 is
> 1
> 2
> 3
> 31
> 4
> Jack
> Low
> Lp
> Upper
>
> I want to order this column in such a way that
> blank spaces first, numbers second(in ascending) and alphabet...
> 1
> 2
> 3
> 4
> 31
> Jack
> Low
> Lp
> Upper
> Please advise.
> Thanks,
> Soura|||On Mon, 5 Dec 2005 07:05:03 -0800, SouRa wrote:
>Hi,
>I have a table with a Varchar column(col1) contains values as below,
>Low
>Upper
>4
>Lp
>31
>1
>2
>Jack
>3
>default order/order by col1 is
>1
>2
>3
>31
>4
>Jack
>Low
>Lp
>Upper
>
>I want to order this column in such a way that
>blank spaces first, numbers second(in ascending) and alphabet...
>1
>2
>3
>4
>31
>Jack
>Low
>Lp
>Upper
>Please advise.
>Thanks,
>Soura
Hi Soura,
ORDER BY CASE WHEN col1 = ' ' THEN 1
WHEN col1 NOT LIKE '%[^0-9]%' THEN 2
ELSE 3
END,
CASE WHEN col1 NOT LIKE '%[^0-9]%' THEN CAST(col1 AS int) END,
col1
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)