0

I have two tables each containing a folder path column. These columns may contain brackets (i.e. Folder1\Folder2\Folder3[Old]\). Joining the two table columns (table1.path = table2.path) will return all records that match from each table. However, there may be a need to find partial paths from one table that exist in another - but a query using a Like operator will ignore records with brackets:

select a.*
from table1 a
cross join table2
where a.path like '%' + b.path + '%'

This does not return the correct results. Is there an escape syntax that needs to be added?

Thank you.

EDIT:

I applied Dai's syntax and it works:

select a.*
from table1 a
cross join table2
where a.path like '%' + replace(replace(b.path, '[', '#['), ']', '#]') +  '%' escape '#'
Craig
  • 145
  • 1
  • 12
  • 1
    Use `WHERE a.path LIKE '%' + REPLACE( REPLACE( b.path, '['', '#[' ), ']', '#]' ) + '%' ESCAPE '#'`. – Dai Jul 12 '23 at 16:27
  • You might want to move that gnarly `CONCAT( REPLACE( REPLACE(...` logic to a `DECLARE` statement located before your query - and to also let you inspect the actual LIKE pattern that's used in the end. – Dai Jul 12 '23 at 16:48
  • NOTE: Not sure why I'm getting "duplicate" post notifications - I don't think my inquiry is similar to the suggested examples as the examples are looking for a specific term to filter/query whereas I'm looking to apply the escape clause to a column that may contain brackets. – Craig Jul 12 '23 at 16:52

0 Answers0