REGEX in stored procedure in "="/like clause-CodePudding

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

CodePudding user response：

You can compare the text without the part between <> and {} :

Here the join condition :

LEFT(ET.VALUE,CHARINDEX('<', ET.VALUE)-1)    RIGHT(ET.VALUE,LEN(ET.VALUE)- CHARINDEX('>', ET.VALUE))
=
LEFT(A.Value,CHARINDEX('{', A.Value)-1)    RIGHT(A.Value,LEN(A.Value)- CHARINDEX('}', A.Value))

But it can be bad in terms of performance because you tranform the key used to join the table.

CodePudding user response：

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)