## Function to do natural sort of string number?

### Function to do natural sort of string number?

Is there a function floating around that will allow natural sorting of
numbers which are strings? What I am referring is that in the series 1, 2,
3, 10 it appears in this order instead of 1, 10, 2, 3. I thought to write a
function to pad leading spaces, but then wasn't sure that was the best
choice.

Thanks for the help.  -cf

### Function to do natural sort of string number?

Cristof,

Use one of the following techniques to make the string sort numerically:

CAST(strcol AS INT)

or

RIGHT(REPLICATE('0', 10) + strcol, 10)

--
BG
SQL Server MVP
http://www.sql.co.il

Quote:> Is there a function floating around that will allow natural sorting of
> numbers which are strings? What I am referring is that in the series 1, 2,
> 3, 10 it appears in this order instead of 1, 10, 2, 3. I thought to write
a
> function to pad leading spaces, but then wasn't sure that was the best
> choice.

> Thanks for the help.  -cf

### Function to do natural sort of string number?

Oh, one other thing to mention is that some of them have a leading letter
and then a number. So the 1st one will not work, but the second should,
right?

Quote:> Cristof,

> Use one of the following techniques to make the string sort numerically:

> CAST(strcol AS INT)

> or

> RIGHT(REPLICATE('0', 10) + strcol, 10)

### Function to do natural sort of string number?

That is correct..

--
Wayne Snyder, SQL Server MVP
Computer Education Services Corporation (CESC), C*te, NC
www.computeredservices.com

I support the Professional Association for SQL Server (PASS) and its user
community of SQL Server Professionals.
www.sqlpass.org

### Function to do natural sort of string number?

Cristof,

Whether the solution works depends where you want the string with
a letter to appear in the result. With Itzik's second suggestion, I believe
you'll get

1
5
58
B2
119
L89

The values will sort like hexadecimal values would sort if there were
additional digits past F.

Steve Kass
Drew University

> Oh, one other thing to mention is that some of them have a leading letter
> and then a number. So the 1st one will not work, but the second should,
> right?

> > Cristof,

> > Use one of the following techniques to make the string sort numerically:

> > CAST(strcol AS INT)

> > or

> > RIGHT(REPLICATE('0', 10) + strcol, 10)

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