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.

SQL SERVER - Order By Numeric Values Formatted as String patindex1

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.

SQL SERVER - Order By Numeric Values Formatted as String patindex2

We need the result in following format.

SQL SERVER - Order By Numeric Values Formatted as String patindex3

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

SQL SERVER - Order By Numeric Values Formatted as String patindex4

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 (https://blog.sqlauthority.com)

SQL Server, SQL String
Previous Post
SQLAuthority News – Windows Azure Training Kit Updated October 2012
Next Post
SQL SERVER – Resolving SQL Server Connection Errors – SQL in Sixty Seconds #030 – Video

Related Posts

11 Comments. Leave new

  • 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)

    Reply
  • Virat Kotharivi
    October 23, 2012 8:20 am

    Superb article. Solution to real time scenario.

    Reply
  • Alternate solution:

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

    Reply
    • Super Nice Harsh!

      Reply
      • 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

  • 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.

    Reply
  • how to use this function if the number is between the string
    Ex: NSE-USD-3Y-SB-OP

    ….

    Reply
  • 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

    Reply
  • Very Good.

    Reply
  • Very Good

    Reply
  • Sanjay Monpara
    October 30, 2012 6:36 pm

    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

    Reply

Leave a Reply