Function to do natural sort of string number?

Function to do natural sort of string number?

Post by Cristof Fal » Thu, 05 Sep 2002 13:44:34



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?

Post by Itzik Ben-Ga » Thu, 05 Sep 2002 15:21:05


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?

Post by Cristof Fal » Thu, 05 Sep 2002 20:51:18


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?

Post by Wayne Snyde » Thu, 05 Sep 2002 21:34:57


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?

Post by Steve Kas » Thu, 05 Sep 2002 21:44:45


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)

 
 
 

1. Sort strings by number

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

2. restoring over an existing on SQL 6.5 database

3. Sorting text string as numbers

4. Liberty JDBC achieves 100% Pure Java Certification

5. oracle function - get numbers from text string

6. How to check the field's name & data type?

7. Function to return UNIQUE number from a string.

8. New backup plan - comments welcome!

9. natural sort order

10. NATURAL 1.2 vs NATURAL 2.

11. natural log (ln^X) function

12. String(numbers) to number

13. Doing a straight SELECT * FROM function: the sorting is not consistent with the ORDER BY clause in the Function