T-SQL, Part I: LIKE Pattern

来源：互联网发布：flicker.js 编辑：程序博客网时间：2024/05/01 19:58

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 StreetWHERE StreetName LIKE IN (‘% Main Street’, ‘foo %’)

To solve the real question, the following scripts helps:

SELECT DISTINCT s.*FROM Street sJOIN 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 ALLSELECT ‘foo %’)SELECT DISTINCT s.*FROM Street sJOIN Query q ON StreetName LIKE q.Result;

是为之记。
Alva Chien
2016.3.23

0 0