0

I have two table

Table_A

id name
1 hisa
2 hipa
3 his_
4 hip_

Table_B:

pattern
is_
ip_
ip_
is_ 
select *
from Table_A A
inner join Table_B B
    on A.name like '%'+B.pattern+'%'

by using this ip_, is_ is matching with ipa and isa as '_' is acting as a wild card

select *
from Table_A A
inner join Table_B B
    on A.name like '%'+B.pattern ESCAPE '\'+'%'

I am getting error for this as I cannot use escape character in like predicate.

My expected output is hip_ should match with any text preceding and followed ip_ followed by any characters.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
lemon chow
  • 303
  • 8
  • You should remove the leading `%` from your `LIKE` pattern because that will allow arbitrary text _before_ the "ip_" string - unless that's intentional? – Dai Jul 13 '23 at 06:41
  • 1
    The [`like` documentation](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver16) explains clearly how to do that. – Dale K Jul 13 '23 at 06:44
  • Why don't you replace underscore in your table first then join the table? After that, you can replace underscore back again. – Lara19 Jul 13 '23 at 07:04
  • Special characters in pattern needs to be escaped. If it is only underscores then `LIKE '%' + REPLACE(pattern, '_', '[_]') + '%'` – Salman A Jul 13 '23 at 08:21
  • 1
    *"I am getting error for this as I cannot use escape character in like predicate."* `ESCAPE` goes after the *entire* clause not in the middle of it: `LIKE '%' + B.pattern + '%' ESCAPE '\'` – Thom A Jul 13 '23 at 08:36

0 Answers0