T-SQL, Part I: LIKE Pattern

The basic usage of LIKE pattern:

%: it would be placed at the end and/or the beginning of a string.

_: it looks at a string, but only for a single character before or after the position of the underscore.

[]: it lets you specify a number of values or a range of values to look for. For example: “%[c-f]%”.

[^…]: Similar to [] option, it lists those items that do not have values within the range specified.

However, consider the question ‘Combination of ‘LIKE’ and ‘IN’ using t-sql’ raised in Stackoverflow: http://stackoverflow.com/questions/6102380/combination-of-like-and-in-using-t-sql

Question: 

SELECT * FROM Street Where StreetName LIKE IN (SELECT name + ‘%’ from CarStreets Where Streets = ‘offroad’ )
           

Or more generic one:

SELECT *
FROM Street
WHERE StreetName LIKE IN (‘% Main Street’, ‘foo %’)
           

To solve the real question, the following scripts helps:

SELECT DISTINCT s.*
FROM Street s
JOIN CarStreets cs ON s.StreetName LIKE cs.name + ‘%’
WHERE cs.Streets = ‘offroad’
           

To solve the generic question, the following scripts helps:

WITH Query(Result) As
(
SELECT ‘% Main Street’ UNION ALL
SELECT ‘foo %’
)
SELECT DISTINCT s.*
FROM Street s
JOIN Query q ON StreetName LIKE q.Result;
           

是為之記。

Alva Chien

2016.3.23