SQL filter from range expression like used in Dynamics Ax 2012

While working with queries in Microsoft Dynamics Ax 2012 (and other versions) you can specify a range like 'B*, D..H, !F*'.

Using above range on column 'column1' in table 'table1', we can expect the same result as the following where-clause:

SELECT *
FROM table1
WHERE column1 LIKE 'B%'
OR (column1 >= 'D' AND column1 <= 'H')
AND column1 NOT LIKE 'F%'

I now need to get the same query result using T-SQL (SQL Server). The ranges I want to apply are stored in a table and can be user modified, so in some way the filter has to be applied during runtime.

If possible to achieve above in SQL Server 2012 that is preferrable.

Trying to solve above I started by replacing all * with %, splitting string (STRING_SPLIT is not supported in SQL Server 2012 but may be implemented as a function) by commas. Then in some way insert result into temporary table, and using the temporary table with a EXISTS SELECT. However, this turns out to be too complicated for me.

Does anyone have an elegant solution to this?

EDIT: MS Dynamics AX converts the range, which is applied to a column, into the WHERE-clause using OR between each comma separated part in the range, except when it is a negation ('!'), in which case AND is used instead. The '..' part is converted to '>=' AND '<='.

Simple example:

table1
id   column1
==   =======
1    Apple
2    Banana
3    Fruit
4    Date

applying above example range on 'column1' would result in following:

table1
id   column1
==   =======
2    Banana
4    Date

Solution

Here's a potential solution that should work in sql server 2012:

DECLARE @colName SYSNAME = 'test'
SELECT  STUFF((
        SELECT  prefix + prefix2 + @colname + ' ' + CASE WHEN btw = 1 THEN '>=' when lke = 1 then 'LIKE' else '=' END + ' ''' + replace(data_left, '*', '%') +'''' 
        +   CASE WHEN btw = 1 
                THEN ' AND ' + @colname + ' <= ' +  '''' +REPLACE(data_right, '*', '%')  + ''''
                ELSE ''
            END + postfix1
        FROM    (
            SELECT  CASE WHEN n.neg = 1 THEN ' AND NOT ' ELSE '  OR ' END AS prefix
            ,   '(' AS prefix2
            ,   CASE WHEN btw = 1 THEN LEFT(dataFixed, charindex('..', dataFixed) - 1) ELSE dataFixed END AS data_left
            ,   CASE WHEN btw = 1 THEN stuff(dataFixed, 1, charindex('..', dataFixed) + 1, '') END AS data_right
            ,   ')' AS postfix1
            ,   lke
            ,   btw
            ,   id
            FROM    (
                    SELECT  RTRIM(LTRIM(Split.a.value('.', 'NVARCHAR(MAX)'))) DATA
                    ,   Split.a.value('let $n := . return count(../*[. << $n]) + 1', 'int') AS id
                    FROM
                    (
                        SELECT  CAST('<X>'+REPLACE(x.s, ',', '</X><X>')+'</X>' AS XML) AS String
                        
                    ) AS A
                    CROSS APPLY String.nodes('/X') AS Split(a)
                ) data
            CROSS apply (
                    SELECT  CASE WHEN LEFT(data, 1) = '!' THEN  1 ELSE 0 END AS neg
                    ,   CASE WHEN data LIKE '%*%' THEN 1 ELSE 0 END AS lke
                    ,   CASE WHEN data LIKE '%..%' THEN 1 ELSE 0 END AS btw
                    ,   case when LEFT(data, 1) = '!' then stuff(data, 1, 1, '') else data end as dataFixed
                ) n
            ) x
        ORDER BY id
        FOR xml path(''), TYPE).value('text()[1]', 'nvarchar(max)')
    , 1, 5, '')
from (
    VALUES  (N'B*, DB..HB, !F*')
    ,   (N'B*, DB*')
    ,   (N'!B')
    ,   (N'!B*')
    ,   (N'!BZZ..ZZABC')
) x(s)

Outputs:

s	(column 2)
B, DB..HB, !F	(test LIKE 'B%') OR (test >= 'DB' AND test <= 'HB') AND NOT (test LIKE 'F%')
B, DB	(test LIKE 'B%') OR (test LIKE 'DB%')
!B	NOT (test = 'B')
!B*	NOT (test LIKE 'B%')
!BZZ..ZZABC	NOT (test >= 'BZZ' AND test <= 'ZZABC')

The basic building blocks are:

data subquery splits a string by "," and generates a id counter, this is equivalent to STRING_SPLIT with a counter parameter in sql server 2022. If you have some special chars in your strings, like <>, you might have to replace them before splitting by this method, since it uses xml
the n cross apply generates flags that controls what kind of string it is, for example negative, like, between and also returns a unnegated string
The rest pretty much concats together the strings and handles correct AND / OR prefixes
Finally, FOR XML generates the proper final strings