REGEX in stored procedure in “=”/like clause

I’ve created a stored procedure with 3 inner joins (please, don’t judge me :D):

SELECT 
    T.Name, A.Value 
FROM
    Table AS T
INNER JOIN
    TableAnotherTable AS TA ON TA.ID_1 = T.ID_1
INNER JOIN
    AnotherTable AS A ON A.ID_2 = TA.ID_2
INNER JOIN
    EndTable AS ET ON ET.Value = A.Value

TableAnotherTable is the association table between Table and AnotherTable. So, this stored procedure works in some cases (if ET.Value is equal to A.Value).

But if:

ET.Value = 'SOMETHINGRIGHT<ABC>THERE'
A.Value= 'SOMETHINGRIGHT{DEFGHILM}THERE'

the stored procedure must also work.

How can I change the last ON of the inner join clause (with “=”) to something like:

Regex("pattern1", E.Value) like Regex("pattern", A.Value)

where pattern and pattern1 are ({.*?}) or (<.*?>)?

Thanks

Examples:

ET.Value = 'HI'
A.Value = 'HI'
true

ET.Value = 'SOMETHINGRIGHT<ABC>THERE'
A.Value = 'SOMETHINGRIGHT{DEFGHILMNOP}THERE'
true

ET.Value = 'HOMESWEETHOME<12345>'
A.Value = 'HOMESWEETHOME{4875928346}'
true

ET.Value = 'EXAMPLE<1234>'
A.Value = 'EG{1234}'
false

ET.Value = 'dog'
A.Value = 'cat'
false

Answer

Unfortunately there is no regexp replace function in SQL Server. The closest you can get is by using PATINDEX to locate the <{ and }>' characters and STUFF` everything in between:

SELECT t.*, CASE WHEN val1_new = val2_new THEN 'match' END
FROM (VALUES
    ('SOMETHING<1>SOMETHING',  'SOMETHING{2}SOMETHING'),
    ('SOMETHINGBLABLA<1>BLA', 'SOMETHINGBLABLA{2}BLA'),
    ('SOME<1>THINGBLA',       'SOMETHING{2}BLABLA'),
    ('dog',                      'dog')
) AS t(val1, val2)
CROSS APPLY (
    SELECT
        PATINDEX('%[<{]%', val1) AS val1_pos1,
        PATINDEX('%[>}]%', val1) AS val1_pos2,
        PATINDEX('%[<{]%', val2) AS val2_pos1,
        PATINDEX('%[>}]%', val2) AS val2_pos2
) AS ca1
CROSS APPLY (
    SELECT
        CASE WHEN val1_pos1 > 0 AND val1_pos2 > 0 THEN STUFF(val1, val1_pos1, val1_pos2 - val1_pos1 + 1, '') ELSE val1 END,
        CASE WHEN val2_pos1 > 0 AND val2_pos2 > 0 THEN STUFF(val2, val2_pos1, val2_pos2 - val2_pos1 + 1, '') ELSE val2 END
) AS ca3(val1_new, val2_new)