SQL SERVER – Order By Numeric Values Formatted as String

When I was writing this blog post I had a hard time to come up with the title of the blog post so I did my best to come up with one. Here is the reason why? I wrote a blog post earlier SQL SERVER – Find First Non-Numeric Character from String. One of the questions was that how that blog can be useful in real life scenario. This blog post is the answer to that question. Let us first see a problem.

We have a table which has a column containing alphanumeric data. The data always has first as an integer and later part as a string. The business need is to order the data based on the first part of the alphanumeric data which is an integer. Now the problem is that no matter how we use ORDER BY the result is not produced as expected. Let us understand this with example.

Prepare a sample data:

-- How to find first non numberic character
USE tempdb
GO
CREATE TABLE MyTable (ID INT, Col1 VARCHAR(100))
GO
INSERT INTO MyTable (ID, Col1)
SELECT 1, '1one'
UNION ALL
SELECT 2, '11eleven'
UNION ALL
SELECT 3, '2two'
UNION ALL
SELECT 4, '22twentytwo'
UNION ALL
SELECT 5, '111oneeleven'
GO
-- Select Data
SELECT *
FROM MyTable
GO

The above query will give following result set.

Now let us use ORDER BY COL1 and observe the result along with Original SELECT.

-- Select Data
SELECT *
FROM MyTable
GO
-- Select Data
SELECT *
FROM MyTable
ORDER BY Col1
GO

The result of the table is not as per expected.

We need the result in following format.

Here is the good example of how we can use PATINDEX.

-- Use of PATINDEX
SELECT ID,
LEFT(Col1,PATINDEX('%[^0-9]%',Col1)-1) 'Numeric Character',
Col1 'Original Character'
FROM MyTable
ORDER BY LEFT(Col1,PATINDEX('%[^0-9]%',Col1)-1)
GO

We can use PATINDEX to identify the length of the digit part in the alphanumeric string (Remember: Our string has a first part as an int always. It will not work in any other scenario). Now you can use the LEFT function to extract the INT portion from the alphanumeric string and order the data according to it.

You can easily clean up the script by dropping following table.

DROP TABLE MyTable
GO

Here is the complete script so you can easily refer it.

-- How to find first non numberic character
USE tempdb
GO
CREATE TABLE MyTable (ID INT, Col1 VARCHAR(100))
GO
INSERT INTO MyTable (ID, Col1)
SELECT 1, '1one'
UNION ALL
SELECT 2, '11eleven'
UNION ALL
SELECT 3, '2two'
UNION ALL
SELECT 4, '22twentytwo'
UNION ALL
SELECT 5, '111oneeleven'
GO
-- Select Data
SELECT *
FROM MyTable
GO
-- Select Data
SELECT *
FROM MyTable
ORDER BY Col1
GO
-- Use of PATINDEX
SELECT ID,
Col1 'Original Character'
FROM MyTable
ORDER BY LEFT(Col1,PATINDEX('%[^0-9]%',Col1)-1)
GO
DROP TABLE MyTable
GO

Well, isn’t it an interesting solution. Any suggestion for better solution? Additionally any suggestion for changing the title of this blog post?

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

11 thoughts on “SQL SERVER – Order By Numeric Values Formatted as String

  1. instead of substring and order it in smaller piece, how about concatenate it and order in bigger one?
    consider:

    select * from mytable order by right(’00000′ + mycol,10)

  2. Alternate solution:

    SELECT ID, Col1 ‘Original Character’ FROM MyTable ORDER BY UNICODE(Col1), PATINDEX(‘%[^0-9]%’, Col1), Col1

      • Thanks,
        Both solutions will work if string has first part as an int and later part as string.
        Minor addition in these scripts can handle strings only has int part(no alphabet characters).
        So, One of two limitations will be removed

        SELECT ID,
        Col1 ‘Original Character’
        FROM MyTable
        ORDER BY LEFT(Col1,PATINDEX(‘%[^0-9]%’,Col1+’a’)-1)

        SELECT ID, Col1 ‘Original Character’ FROM MyTable ORDER BY UNICODE(Col1), PATINDEX(‘%[^0-9]%’, Col1+’a’), Col1

  3. Hi,

    I also have an alternate solution for your “challenge”:

    select ID, Col1 as Original_String, SUBSTRING(Col1, 0, PATINDEX(‘%[a-z]%’, Col1)) as Numeric_Character
    from MyTable
    order by Numeric_Character

    I think it is quite elegant…. SUBSTRING(Col1, 0, PATINDEX(‘%[a-z]%’, Col1))… SUBSTRING returns character data if expr (Col1) is character type (our case here) so the result is ok…

    You are doing a great job here!

    Thanks,
    Eduard U.

  4. Order by Numeric Values formatted as string, then the correct order shouldn’t be like this?

    SELECT ID,
    Col1 ‘Original Character’
    FROM MyTable
    ORDER BY CAST(LEFT(Col1,PATINDEX(‘%[^0-9]%’,Col1)-1) AS INT)
    GO

  5. If you want result in numeric order then you can use this code,
    SELECT ID,
    LEFT(Col1,PATINDEX(‘%[^0-9]%’,Col1)-1) ‘Numeric Character’,
    Col1 ‘Original Character’
    FROM MyTable
    ORDER BY cast(LEFT(Col1,PATINDEX(‘%[^0-9]%’,Col1)-1) as int)
    GO

    ID Numeric Character Original Character
    1 1 1one
    3 2 2two
    2 11 11eleven
    4 22 22twentytwo
    5 111 111oneeleven

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s