March 12, 2007 at 7:39 am
Hi
I've a table called 'MySort' and the record is as below:
SortColName
1
200
abc
100
10
45
20
I want to display it as:
SortColName
1
10
20
45
100
200
abc
The datatype is nVarchar(30). I want to sort on numeric part first and then on character.
Amit
March 12, 2007 at 11:55 am
Something like this?:
create
table #t (
C1 nvarchar(
30))
insert
into #t values(1)
insert
into #t values(200)
insert
into #t values('abc')
insert
into #t values('cbc')
insert
into #t values(100)
insert
into #t values(10)
insert
into #t values(45)
insert
into #t values(20)
select
row_number() over(order by convert(int,c1)) as 'RowNumber',c1 from #t where isnumeric(c1) = 1
union
select
row_number() over(order by c1) + (select count(*) from #t where isnumeric(c1) = 1) as 'RowNumber' ,c1 from #t where isnumeric(c1) = 0
order
by RowNumber
March 12, 2007 at 11:59 am
CREATE TABLE #Temp (Field1 NVARCHAR(10))
INSERT INTO #Temp (Field1) SELECT '20'
SELECT '1'
UNION
SELECT '10'
UNION
SELECT '45'
UNION
SELECT '100'
UNION
SELECT '210'
UNION
SELECT 'abc'
UNION
SELECT '20'
DECLARE @LEN INT
SELECT @LEN=MAX(LEN(Field1)) FROM #Temp
SELECT Field1 FROM #Temp
ORDER BY RIGHT ((REPLICATE('0',@LEN) + Field1 + '+'),(@LEN + 1))
Result
Field1
----------
1
10
20
45
100
210
abc
March 12, 2007 at 10:49 pm
Hi
Plz check the result set. Its not like that. It should arrange numeric part first and then character part. e.g. 10,20,45,100,200,abc
Row_number() is not a SQL function. Plz check it again.
Amit
March 13, 2007 at 8:10 am
Post in a forum for TSQL on SQL 2005 you will get a SQL 2005 answer.
Both solutions given will give the result set you asked for in the initial post, on SQL 2005.
March 16, 2007 at 5:27 am
Or even more simpler...
SELECT SortColName FROM Table1 WHERE ISNUMERIC(SortColName) = 1 ORDER BY LEN(SortColName), SortColName
UNION ALL
SELECT SortColName FROM Table1 WHERE ISNUMERIC(SortColName) = 0 ORDER BY LEN(SortColName), SortColName
N 56°04'39.16"
E 12°55'05.25"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply