Sort strings by number

Sort strings by number

Post by Aldi » Wed, 24 Nov 1999 04:00:00



Hello all,

 I need to sort character strings (house numbers) which are digits (like
'123') or digits-letters (like '123A') . I need to sort them BY NUMBER, for
ex.
 '2'
 '99'
 '99A'
 '100'.

I cannot use

    SELECT house_no
     FROM houses
     ORDER BY CONVERT(int, house_no), house_no

because CONVERT(int, house_no) returns an error when it encounters a letter
in string.
Does anybody have any idea?
Thanks.

Aldis

 
 
 

Sort strings by number

Post by Mischa Sandber » Sat, 27 Nov 1999 04:00:00


Ooh, this will look complicated, but:

SELECT house_no FROM houses
ORDER BY
    CONVERT(int,
            ISNULL(SUBSTRING(house_no, 1,
                                                PATINDEX("%[A-Za-z]%",
house_no)), 0),
    SUBSTRING(house_no, PATINDEX("%[A-Za-z]%", house_no, 255)


> Hello all,

>  I need to sort character strings (house numbers) which are digits (like
> '123') or digits-letters (like '123A') . I need to sort them BY NUMBER, for
> ex.
>  '2'
>  '99'
>  '99A'
>  '100'.

> I cannot use

>     SELECT house_no
>      FROM houses
>      ORDER BY CONVERT(int, house_no), house_no

> because CONVERT(int, house_no) returns an error when it encounters a letter
> in string.
> Does anybody have any idea?
> Thanks.

> Aldis

--

CIRKIDS! http://mischas.hypermart.net