Sql server: How to sort alphanumeric string data in table

Introduction: In this article I am going to explain how to sort alphanumeric string data/records in sql table. 

In previous articles I have explained How to Update table data using inner join in sql server and Drop or truncate parent table by dropping all foreign key constraints and CTE recursive query to get parent child hierarchy with levels and Row_number(), rank(), dense_rank(), ntile() ranking functions

Implementation:  Let’s create a temporary table having alphanumeric data using the following script.

GO

IF OBJECT_ID('tempdb.dbo.#tbTest', 'U') IS NOT NULL

  DROP TABLE #tbTest;

GO 

CREATE TABLE #tbTest

(

            Data VARCHAR(20)

)

GO

INSERT INTO #tbTest

VALUES

('11'),

('aa20'),

('88'),

('2'),

('7678'),

('mgd'),

('121'),

('WstBg1'),

('WstBg11'),

('WstBg111'),

('11a'),

('aa'),

('111'),

('aaa'),

('ab'),

('gh');

View table data.

SELECT Data AS UnsortedData FROM #tbTest

UnsortedData

11

aa20

88

2

7678

mgd

121

WstBg1

WstBg11

WstBg111

11a

aa

111

aaa

ab

gh

As we can see data is not in any order. Lets try to sort it by the Data column.

SELECT Data AS NormalSortedData FROM #tbTest ORDER BY Data

NormalSortedData

11

111

11a

121

2

7678

88

aa

aa20

aaa

ab

gh

mgd

WstBg1

WstBg11

WstBg111

As we can see the results are not in proper sort order; since sql consider all data as string.

Sort alphanumeric data

Here is the way to sort the alphanumeric data in proper fashion.I found it somewhere on the internet and it works very well.

When sorting alphanumeric strings, we need to extract all numbers and have two types of strings:

• Strings composed of non-digits,
• Strings composed of digits 0 to 9, i.e. such string can be converted to numbers.  

Using ISNUMERIC inbuilt function we can test whether the expression is numeric or not. This function returns 1 if the expression is numeric, otherwise it returns 0.

e.g.

SELECT ISNUMERIC('123')-- It will return 1

SELECT ISNUMERIC('abc')-- It will return 0

Final Query will be :

SELECT Data AS SortedData

FROM #tbTest

ORDER BY

CASE WHEN ISNUMERIC(Data)=0 THEN Data ELSE '0' END, --Alphabets

CASE WHEN ISNUMERIC(Data) =1 THEN CONVERT(INT, Data) ELSE -1 END --NUMBERS

SortedData

2

11

88

111

121

7678

11a

aa

aa20

aaa

ab

gh

mgd

WstBg1

WstBg11

WstBg111

As you can see the alphanumeric string data is sorted in proper manner.

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.

• Tweet
• Share
• Share
• Share

Related Post