Sort by first matching number then by second matching number and so on in SQL

Question

sort by first matching number then by second matching number in SQL

Suppose, I have a table entries as following.

Btc0504
Btc_0007_Shd_01
Btc_007_Shd_01
Bcd0007_Shd_7
ptc00044
Brg0007_Shd_6
Btc0075_Shd
Bcc43
MR_Tst_etc0565
wtc0004_Shd_4
vtc_Btc0605

so it should bring records as following.

wtc0004_Shd_4
Bcc43
ptc00044
Btc_007_Shd_01
Btc_0007_Shd_01
Brg0007_Shd_6
Bcd0007_Shd_7
Btc0075_Shd
Btc0504
MR_Tst_etc0565
Btc_vtc0605

So basically it sorts by numbers only, words are only separator of numbers.

Here middle strings can be of any numbers.

They are not fixed and this pattern is also not fixed.

so there can be more strings and numbers with row. i.e. a1b2c3d4e5..., u7g2u9w2s8...

So require dynamic solution.

Example table is given below.

http://rextester.com/IDQ22263

Answer 1

Assuming you would have 2 number blocks at most and each number would be 10 digits at most, I created a sample CLR UDF like this for you (DbProject - SQL CLR Database project):

using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString CustomStringParser(SqlString str)
    {
        int depth = 2; // 2 numbers at most
        int width = 10; // 10 digits at most

        List<string> numbers = new List<string>();
        var matches = Regex.Matches((string)str, @"\d+");
        foreach (Match match in matches)
        {
            numbers.Add(int.Parse(match.Value).ToString().PadLeft(width, '0'));
        }
        return string.Join("", numbers.ToArray()).PadRight(depth*width);
    }
}

I added this to the 'test' database as follows:

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[ufn_MyCustomParser]') AND
                    type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ) )
  DROP FUNCTION [dbo].[ufn_MyCustomParser]
GO
IF EXISTS ( SELECT  *
            FROM    sys.[assemblies] AS [a]
            WHERE   [a].[name] = 'DbProject' AND
                    [a].[is_user_defined] = 1 )
  DROP ASSEMBLY DbProject;
GO


CREATE ASSEMBLY DbProject
FROM 'C:\SQLCLR\DbProject\DbProject\bin\Debug\DbProject.dll'
WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION ufn_MyCustomParser ( @csv NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS EXTERNAL NAME
  DbProject.[UserDefinedFunctions].CustomStringParser;
GO

Note: SQL server 2012 (2017 has strict security problem that you need to handle).

Finally tested with this T-SQL:

declare @MyTable table (col1 varchar(50));
insert into @MyTable values
('Btc0504'),
('Btc0007_Shd_7'),
('Btc0007_Shd_01'),
('Btc0007_Shd_6'),
('MR_Tst_Btc0565'),
('Btc0004_Shd_4'),
('Btc_BwwwQAZtc0605'),
('Btc_Bwwwwe12541edddddtc0605'),
('QARTa1b2');
SELECT * FROM @MyTable
ORDER BY dbo.ufn_MyCustomParser(col1);

Output:

col1
QARTa1b2
Btc0004_Shd_4
Btc0007_Shd_01
Btc0007_Shd_6
Btc0007_Shd_7
Btc0504
MR_Tst_Btc0565
Btc_BwwwQAZtc0605
Btc_Bwwwwe12541edddddtc0605

Answer 2

Below query does the following: it uses patindex function, to extract index in a string of a pattern:

1. firstly, it extracts beginning of number, searching for a digit.

2. Secondly, it extracts end of a number searching for digit followed by non-digit.

Having done that, we have everything to extract a nuber from a string and sort by it after converting (casting) it to an integer.

Try this query:

declare @tbl table (col1 varchar(50));
insert into @tbl values
('Btc0504'),
('Btc0007_Shd_7'),
('Btc0007_Shd_6'),
('MR_Tst_Btc0565'),
('Btc0004_Shd_4'),
('Btc_Btc0605');

select col1 from (
    select col1,
           PATINDEX('%[0-9]%', col1) [startIndex],
           case PATINDEX('%[0-9][^0-9]%', col1) when 0 then LEN(col1) else     PATINDEX('%[0-9][^0-9]%', col1) end [endIndex]
    from @tbl
) [a]
order by CAST(SUBSTRING(col1, startIndex, endIndex - startIndex + 1) as int)

I came up with another solution, which is very compact and more general:

;with cte as (
    select 1 [n], col1, STUFF(col1, PATINDEX('%[^0-9]%', col1), 1, '.') refined_col1 from @tbl
    union all
    select n+1, col1, STUFF(refined_col1, PATINDEX('%[^0-9.]%', refined_col1), 1, '.') from cte
    where n < 100 -- <--this number must be greater than the greatest amount of non-digits in a col1, this way, you are sure that you'll remove all unnecesary characters
)

select col1, refined_col1 from cte
where PATINDEX('%[^0-9.]%', refined_col1) = 0
order by CAST(replace(refined_col1, '.', '') as int)
option (maxrecursion 0)

Answer 3

I will begin my answer by saying that the best long term solution for you is to fix your data model. If you have need to use the various portions of the entry in queries, for sorting, etc., then consider storing them in separate bona-fide columns.

That being said, one workaround is to use basic string operations to extract the two components you want so use for sorting. Note carefully that we have to cast them to numbers, because otherwise they won't sort properly as text.

SELECT *
FROM entries
ORDER BY
    CAST(SUBSTRING(entry, PATINDEX('%Btc[0-9]%', entry) + 3, 4) AS INT),
    CASE WHEN CHARINDEX('Shd_', entry) > 0
         THEN
         CAST(SUBSTRING(entry,
                        CHARINDEX('Shd_', entry) + 4,
                        LEN(entry) - CHARINDEX('Shd_', entry) -4) AS INT)
         ELSE 1 END;

Demo

Answer 4

At the beginning I does not recommend the next approach for performance aspect , you should fix the root cause of your data.

For handling dynamic inputs, I Think you should create UDF function for extracting the numbers only like next:-

CREATE FUNCTION dbo.udf_ExtratcNumbersOnly
(@string VARCHAR(256))
RETURNS int
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%',@string) <> 0
    SET @string = STUFF(@string,PATINDEX('%[^0-9]%',@string),1,'')
    RETURN cast (@string as int)
END
GO

Then use it as next:-

declare @MyTable table (col1 varchar(50));
insert into @MyTable values
('Btc0504'),
('Btc0007_Shd_7'),
('Btc0007_Shd_6'),
('MR_Tst_Btc0565'),
('Btc0004_Shd_4'),
('Btc_BwwwQAZtc0605'),
('Btc_Bwwwwe12541edddddtc0605'),
('QARTa1b2c3d4e5');

select * from @MyTable 
order by (dbo.udf_ExtratcNumbersOnly(col1))

Result:-

Btc0004_Shd_4
Btc0007_Shd_6
Btc0007_Shd_7
Btc0504
MR_Tst_Btc0565
Btc_BwwwQAZtc0605
QARTa1b2c3d4e5
Btc_Bwwwwe12541edddddtc0605

Demo.

Answer 5

You can use a tally table/numbers table to get each character and find only numbers and then combine the numbers in order to form a string(which can be casted into bigint). Then you can order based on this string.

See working demo

; with numbers as (
    select top 10000
        r= row_number() over( order by (select null))
    from sys.objects o1 
        cross join sys.objects o2
   )

, onlynumbers as
(
    select * from t 
    cross apply
    ( select part =substring(num,r,1),r
      from numbers where r<=len(num)
     )y
    where part  like '[0-9]' 
)

, finalorder as
(
    select num,cast(replace(stuff
    ((
        select ','+part
        from onlynumbers o2 
        where o2.num=o1.num
        order by o2.r
        for xml path('')
        ),1,1,''),',','') as bigint) b
  from onlynumbers o1
  group by num
   )
 select num from finalorder order by b asc