In this article, we will see how to sort the alphanumeric columns. See the below example,
Create a Temporary Table with column name is ID. It contains the alphanumeric values.
CREATE TABLE #temp(id VARCHAR(20))
INSERT INTO #temp VALUES('101')
INSERT INTO #temp VALUES('aaa')
INSERT INTO #temp VALUES('aa')
INSERT INTO #temp VALUES('201')
INSERT INTO #temp VALUES('101')
INSERT INTO #temp VALUES('aba')
INSERT INTO #temp VALUES('301')
INSERT INTO #temp VALUES('53')
INSERT INTO #temp VALUES('Aa')
INSERT INTO #temp VALUES('aAa')
INSERT INTO #temp VALUES('aA')
INSERT INTO #temp VALUES('Aba')
Now see the normal selection
SELECT * FROM #temp
The output is
Normal selection with order
SELECT * FROM #temp ORDER BY ID
The output is
Now we see the selection order with right way.
SELECT * FROM #T
ORDER BY
CASE WHEN ISNUMERIC(id) = 1 THEN right(Replicate('0',21) + id, 21)
WHEN ISNUMERIC(id) = 0 then Left(id + Replicate('',21), 21)
ELSE id
END
The output is ,
Now we see the selection without Case sensitive order with right way.
SELECT * FROM #temp
ORDER BY
CASE WHEN ISNUMERIC(id) = 1 THEN right(Replicate('0',21) + id, 21)
WHEN ISNUMERIC(id) = 0 then Left(id + Replicate('',21), 21)
ELSE id
END
COLLATE SQL_Latin1_General_CP850_CS_AS
The output is
Now we see the selection with Case sensitive order.
SELECT * FROM #temp
ORDER BY
CASE WHEN ISNUMERIC(id) = 1 THEN right(Replicate('0',21) + id, 21)
WHEN ISNUMERIC(id) = 0 then Left(id + Replicate('',21), 21)
ELSE id
END
COLLATE Latin1_General_CS_AI
The output is
05:37 |
Category:
SQL SERVER
|
0
comments
Comments (0)