by Aldi » Wed, 24 Nov 1999 04:00:00
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
by Mischa Sandber » Sat, 27 Nov 1999 04:00:00
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)
> 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
1. Sorting text string as numbers
Hi,
I want to sort on a text string that are formed like this:
1.1
1.2
..
2.1
..
3.1
..
10.1
Sorting on this will plaze 10.1 at the beginning.
Anyone know how to sort this numeric and not as text ?
2. SQL Mail (To Use or Not To Use)
3. Function to do natural sort of string number?
4. Getting SQL table row count in Access ?
6. Inserting an image to the DB
9. How to Sort Alphanumeric Strings Based on Their Numeric Portions
10. Getting a sorted list of values in a string
12. Sort A String