REGEX in stored procedure in "="/like clause

Question

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 = 'SOME\THING\RIGHT\<ABC>\THERE'
A.Value= 'SOME\THING\RIGHT\{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 = 'SOME\THING\RIGHT\<ABC>\THERE'
A.Value = 'SOME\THING\RIGHT\{DEFGHILMNOP}\THERE'
true

ET.Value = 'HOME\SWEET\HOME\<12345>\'
A.Value = 'HOME\SWEET\HOME\{4875928346}\'
true

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

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

Answer 1

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'),
    ('SOMETHING\BLABLA\<1>\BLA', 'SOMETHING\BLABLA\{2}\BLA'),
    ('SOME\<1>\THING\BLA',       '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)