CODEWITHSUNDEEP
sql
Matti
Matti

Reputation: 391

Is using LIKE '%%x%%' different than using LIKE '%x%'?

I am looking at a stored procedure written by another programmer and I see they are using

SELECT * FROM dbo.names WHERE name LIKE '%%st%%ack%%flow%%'

and I have never seen this notation before. My assumption is that this would behave the same way as

SELECT * FROM dbo.names WHERE name LIKE '%st%ack%flow%'

but I have never seen this before and I see nothing online that discusses using double percents in a LIKE statement like this before. Can someone explain the possible difference between the two?

I expect that using %% and % in a LIKE statement are interchangeable and they should never produce different results.

Upvotes: 1

Views: 638

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

There is literaly no difference between:

SELECT * FROM dbo.names WHERE name LIKE '%%st%%ack%%flow%%'

and

SELECT * FROM dbo.names WHERE name LIKE '%st%ack%flow%'

% wildcard allows any or no characters.

The only difference is when % is escaped:

CREATE TABLE names(name VARCHAR(10));
INSERT INTO names(name) VALUES('%b');
INSERT INTO names(name) VALUES('%ab');

SELECT * FROM names WHERE name LIKE '%%b';
SELECT * FROM names WHERE name LIKE '%%b' ESCAPE '%';

db<>fiddle demo

Upvotes: 4

Related Questions