Click here to Skip to main content
14,271,759 members

How Do I Use SQL Server to Sort Alphanumeric Values?

Rate this:
4.82 (5 votes)
Please Sign up or sign in to vote.
4.82 (5 votes)
14 Nov 2014MIT
Sort Alphanumeric Values with SQL Server

Introduction

Recently, a reader asked me how to sort an alpha numeric field in SQL in natural order.

When we have numeric fields, we expect values to be sorted in ordinal order, for example: 1,2,5,7,10,11,15,20,21.

However when these numbers are in character field, such as char or varchar, the sort becomes alphabetic and the ordering not what we may wish: 1,10,11,15,2,20,21,5,7.

For numerals in a text field we could easily convert the values to numeric values and sort; however, this option isn’t available if the column values contain alphanumeric values.

NOTE: This is somewhat of an intermediate to advanced article. I’m assuming that you already know of the LEFT, SUBSTRING, and CONVERT functions.

Sorting Alphabetic versus Alphanumeric

For this example, we’ll assume we have defined a table named Section that consists of one varchar(50) field named section. Here is the definition:

Image 1

Our table contains the following alphanumeric values:

1
AB1
A1
B2
A11
B20
B21
AB10
B3
AB100
2
B1
B32
11
A10

Which we want to sort alphanumerically as:

1
2
11
A1
A10
A11
AB1
AB10
AB100
B1
B2
B3
B20
B21
B32

But when you try:

SELECT Section
FROM Section
ORDER BY Section

The results aren’t as we expect:

1
11
2
A1
A10
A11
AB1
AB10
AB100
B1
B2
B20
B21
B3
B32

The items in red aren’t sorted according to alphanumeric order. The reason for this is that SQL sorts character by character from left to right. In its mind, the correct alphabetical sequence is …B1, B2, B20, B21, B3,…

Once nice feature about SQL is that you can sort an expression. Because of this, we can create our own formula to sort.

There are two approaches. The first is a simple approach that will work with my example and the second is much more complex, but works in general with any alphanumeric data.

First the Simple Case

In the simple case, the idea is to separate the alpha characters from the numeric, then sort by the alpha characters, convert the numeric portion to a number and sort.

Conceptually, our SQL statement will look like:

SELECT Section
FROM Section
ORDER BY SectionAlpha, SectionNumeric

We really don’t have columns for SectionAlpha and SectionNumeric, instead we are going to use two expressions to separate the character portion of the data from the numeric.

The trick is to know where the number begins. To do this, we can use a special formula called PATINDEX.

This formula can be used to find the character position within a string that matches a particular pattern, such as ‘%[0-9]%’.

We talked about patterns in our article on pattern matching. You may recall that %[0-9]% means to return a match, if any position in the string is in the range of characters 0-9.

For instance PATINDEX(‘SQL2005’, ‘%[0-9]%’) returns 4, since the numeral 2 is in the fourth position.

Stated differently, the first character to match %[0-9]% is 2, which is in position 4.

Image 2

Knowing where the number starts is half the story. We now need to separate the value into a character and numeric portion. To do this, we’ll use two string functions:

  • LEN(expression) – Returns the number of characters found in an expression. LEN(‘MICHIGAN’) returns 8.
  • LEFT(expression, length) – Starting from the left, position 1, this function returns the length amount of characters. LEFT(‘Airplane’,3) returns ‘Air’.
  • SUBSTRING(expression, start, length) – Starting at a specified position, this function returns the specified amount of characters from a character expression. SUBSTRING(‘Airplane’,4,2) returns ‘pl’.

Using the diagram above, you can see that everything to the left of the number is a character.

To get the alpha portion, we use the following expression:

LEFT(Section,PATINDEX('%[0-9]%',Section)-1)

The expression instructs SQL to get all the characters to the left of the first numeric character in the section column.

Then numeric portion is obtained using:

SUBSTRING(Section,PATINDEX('%[0-9]%',Section),LEN(Section))

This instructs SQL to get all characters, starting where the first numeric character is found until the end of the value.

Finally, we take the numeric string we find and convert it to an integer using the CONVERT function.

The final format for the selection statement is:

SELECT Section
FROM dbo.Section
ORDER BY LEFT(Section, PATINDEX('%[0-9]%', Section)-1), -- alphabetical sort
         CONVERT(INT, SUBSTRING(Section, PATINDEX('%[0-9]%', Section), LEN(Section))) -- numerical

If you want to try out the example, start up SQL Server Management Studio and paste the following code into a new query window.

USE [AdventureWorks2012_Data]
CREATE TABLE dbo.Section
(
       Section varchar(50) NULL
)
INSERT INTO dbo.Section (Section.Section) VALUES ('1')
INSERT INTO dbo.Section (Section.Section) VALUES ('AB1')
INSERT INTO dbo.Section (Section.Section) VALUES ('A1')
INSERT INTO dbo.Section (Section.Section) VALUES ('B2')
INSERT INTO dbo.Section (Section.Section) VALUES ('A11')
INSERT INTO dbo.Section (Section.Section) VALUES ('B20')
INSERT INTO dbo.Section (Section.Section) VALUES ('B21')
INSERT INTO dbo.Section (Section.Section) VALUES ('AB10')
INSERT INTO dbo.Section (Section.Section) VALUES ('B3')
INSERT INTO dbo.Section (Section.Section) VALUES ('AB100')
INSERT INTO dbo.Section (Section.Section) VALUES ('2')
INSERT INTO dbo.Section (Section.Section) VALUES ('B1')
INSERT INTO dbo.Section (Section.Section) VALUES ('B32')
INSERT INTO dbo.Section (Section.Section) VALUES ('11')
INSERT INTO dbo.Section (Section.Section) VALUES ('A10')
SELECT Section
FROM dbo.Section
--Show normal Sort
SELECT Section
FROM dbo.Section
ORDER BY Section
--Show AlphaNumberic Sort
SELECT Section
FROM dbo.Section
ORDER BY LEFT(Section,PATINDEX('%[0-9]%',Section)-1), -- alphabetical sort
         CONVERT(INT,SUBSTRING(Section,PATINDEX('%[0-9]%',Section),LEN(Section))) -- numerical sort
--cleanup our work
DROP Table dbo.Section

When you run this code, you’ll see three results:

  1. The unsorted table
  2. The table sorted with the built-in sort, and
  3. The table sorted alphanumerically

Hopefully through this example, you’ve see that it is possible to construct a customized sort expression to sort columns alphanumerically.

However, as you have noticed, this example only works when the alphanumeric value consists of one alpha and one numeric section. What about values such as MI10.5AB23 or MI200.5AB500? Is there an easy way to sort these values alphanumerically?

General Case for Sorting Alphanumeric Values

I recently came across a really good article by Craig Finck on Alphanumeric Sorting In MSSQL. This article is really advanced, and the code complicated, but the short of the matter is that you can create a UDF (User Defined Function) to create the alpha numeric sort value.

Once you read the article, you’ll see how you can define a UFD called fn_CreateAlphanumericSortValue, which you can then call from your SQL. Later on, I will teach you how to create and define UDFs, but since this article really provides a great general solution to the problem, I wanted to include it as part of this article, so those looking for a really workable solution would know of it, and be able to use it.

Using our example, you could use the UDF to sort as:

SELECT Section
FROM dbo.Section
ORDER BY dbo.fn_CreateAlphanumericSortValue(section)

At this point, you may be saying hey! Why did you go through that first example and all those functions instead of just showing us this general case and the UDF?

Two reasons:

  1. The UDF is pretty complicated and very advanced. It would be hard to teach, and I wanted you to learn something. Hopefully, you leaned some more about sorts, and also how to use PATINDEX, LEFT, and SUBSTRING to manipulate character values.
  2. Using user defined functions can solve a lot of problems, but beware that they can cause performance issues. If you inspect this definition, you’ll see there is a lot of program logic. This logic has to be run for each record. This can severely impact the performance of any large query that used this function to soft values.

Because of this, I wouldn’t recommend using this function for every case; It works good for complicated cases, but for simple cases, such as the one I explained, it is easier to use simple string functions to break out the value and sort.

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

essentialSQL
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here ==> http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
QuestionFormat Pin
Nelek5-Mar-15 1:01
protectorNelek5-Mar-15 1:01 
GeneralPerformance, etc. Pin
Craig Finck21-Dec-14 11:00
professionalCraig Finck21-Dec-14 11:00 
GeneralRe: Performance, etc. Pin
essentialSQL19-Jan-15 16:26
memberessentialSQL19-Jan-15 16:26 
SuggestionBatter Option Pin
Saddamhusen Uadanwala16-Dec-14 18:37
professionalSaddamhusen Uadanwala16-Dec-14 18:37 
select ID,Name from tablename order by dbo.fnMixSort(ID)

CREATE FUNCTION [dbo].[fnMixSort]
(
   @ColValue NVARCHAR(255)
)
RETURNS NVARCHAR(1000)
AS

BEGIN
   DECLARE @p1 NVARCHAR(255),
      @p2 NVARCHAR(255),
      @p3 NVARCHAR(255),
      @p4 NVARCHAR(255),
      @Index TINYINT

   IF @ColValue LIKE '[a-z]%'
      SELECT 
         @Index = PATINDEX('%[0-9]%', @ColValue),
    	 @p1 = LEFT(CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END + REPLICATE(' ', 255), 255),
         @ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, 255) END
   ELSE
      SELECT @p1 = REPLICATE(' ', 255)

   SELECT @Index = PATINDEX('%[^0-9]%', @ColValue)

   IF @Index = 0
      SELECT
         @p2 = RIGHT(REPLICATE(' ', 255) + @ColValue, 255),
         @ColValue = ''
   ELSE
      SELECT
         @p2 = RIGHT(REPLICATE(' ', 255) + LEFT(@ColValue, @Index - 1), 255),
         @ColValue = SUBSTRING(@ColValue, @Index, 255)

   SELECT @Index = PATINDEX('%[0-9,a-z]%', @ColValue)

   IF @Index = 0
      SELECT @p3 = REPLICATE(' ', 255)
   ELSE
      SELECT
         @p3 = LEFT(REPLICATE(' ', 255) + LEFT(@ColValue, @Index - 1), 255),
    	 @ColValue = SUBSTRING(@ColValue, @Index, 255)

   IF PATINDEX('%[^0-9]%', @ColValue) = 0
      SELECT @p4 = RIGHT(REPLICATE(' ', 255) + @ColValue, 255)
   ELSE
      SELECT @p4 = LEFT(@ColValue + REPLICATE(' ', 255), 255)

   RETURN @p1 + @p2 + @p3 + @p4

END


modified 5-Mar-15 7:00am.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Technical Blog
Posted 14 Nov 2014

Stats

27.9K views
5 bookmarked