Sorting text string as numbers

Sorting text string as numbers

Post by Tott » Sat, 11 Oct 2003 14:34:48



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 ?
 
 
 

Sorting text string as numbers

Post by David Porta » Sat, 11 Oct 2003 14:49:03


CREATE TABLE foo (x VARCHAR(10) PRIMARY KEY)

INSERT INTO foo VALUES ('1.1')
INSERT INTO foo VALUES ('1.2')
INSERT INTO foo VALUES ('2.1')
INSERT INTO foo VALUES ('3.1')
INSERT INTO foo VALUES ('10.1')

SELECT x
 FROM foo
 ORDER BY
  CAST(LEFT(x,CHARINDEX('.',x)-1) AS INTEGER),
  CAST(SUBSTRING(x,CHARINDEX('.',x)+1,10) AS INTEGER)

--
David Portas
------------
Please reply only to the newsgroup
--

 
 
 

Sorting text string as numbers

Post by David Porta » Sat, 11 Oct 2003 14:51:43


Or maybe:

SELECT x
 FROM foo
 ORDER BY
  CAST(x AS DECIMAL(10,1))

--
David Portas
------------
Please reply only to the newsgroup
--

 
 
 

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. Data-bound control like a DBGrid that will work in VB?

3. Function to do natural sort of string number?

4. BCNN FoxPro Newsletter May 1994 Issue

5. Sorting text and number(the 2 in the same field)

6. Linux NET-SNMP MRTG Oracle

7. Sorting Numbers in Text Field

8. oracle function - get numbers from text string

9. Extracting text strings and numbers

10. Robust number to text string

11. Most efficient sort text file routine, sort algorithms

12. String(numbers) to number