SQL Server special character query and use of escape

Time:2021-1-19

An old project of the company, SQL Server

In fuzzy query, the query condition contains, and then the query result is always empty

If there is no character after escape character in like mode, the mode is invalid and like returns false. If the character after the escape character is not a wildcard, the escape character is discarded and the character after the escape character is treated as a regular character in the pattern. This includes percent (%), underscores (_ )And the left bracket ([) wildcard, if they are contained in double brackets ([]).

 

The following is about the method of retrieving special characters in SQL script retrieval

Use escape to escape keyword usage

CREATE TABLE test (info VARCHAR(50))
GO
INSERT INTO test
VALUES ('[web.]maomao365.com'),
('web.maomao365.com')
GO

select * from test where info like '%[web.]%'
---Output 2 lines of data, and the like condition is defined as containing

SELECT * FROM test 
WHERE info LIKE  '%\[address.]%' 
---The use of slash is similar to escape, but the escape fails and returns an empty data row
go
SELECT * FROM test
WHERE info LIKE '%$[web.]%' ESCAPE '$'


SELECT * FROM test
WHERE info LIKE '%@[web.]%' ESCAPE '@'
---Use escape to define @ $as the escape character of the above SQL script

go

drop table test