Does double-escaping a wildcard in SQL nullify the escape?

Question

I have an interesting behavior that I would like to better understand so I don't get hung up misusing it by accident.

The following is an example of escaping a '%' in a WHERE clause:

select * from #z where b like '%e%' ESCAPE 'e'

In this table with these values:

create table #z (a int, b varchar(10))
insert into #z values (1, 'e25%')
insert into #z values (2, '25')
insert into #z values (3, '18%')
insert into #z values (4, 'cab')
insert into #z values (5, '2%')

selects the three rows with the % at the end.

However, if I replace like '%e%' with like 'ee%' it selects only the first row. Thus it appears that double-escaping the % is like a double-negative, and undoes the escape, thus making the % back into the wildcard again. Am I understanding this correctly? Or is there something else going on here?

Answer 1

When you write an escape character then next character after the escape is treated literally, and not with its special meaning. In this case the first character following the escape is 'e' so this becomes a literal 'e' and not an escape character as it otherwise would be.

The same principle applies in many languages where if you want a backslash you have to write two backslashes - the first backslash escapes the second so that it loses its special meaning.

Answer 2

I don't actually use ESCAPE: I've always found it odd. In this case, you're escaping the 2nd e so you get like 'e%' (with no escape)

Personally, I'd use this like '%[%]' (with no escape) where possible