折腾:
期间,搜:
sequel pro filter wildcard
sequel wildcard filter
sequel wildcard query
结果搜出来一堆的,MySQL中,如何使用通配符去实现搜索查询的。
找了一堆的参考资料:
How to use wildcard characters in the SQL-SELECT statement in Visual FoxPro
“To broaden the selections of a structured query language (SQL-SELECT) statement, two wildcard characters, the percent sign (%) and the underscore (_), can be used.
The percent sign is analogous to the asterisk (*) wildcard character used with MS-DOS. The percent sign allows for the substitution of one or more characters in a field.
The underscore is similar to the MS-DOS wildcard question mark character. The underscore allows for the substitution of a single character in an expression. Three examples showing the use of these characters in SQL-SELECT statements are provided below. The examples use the Customer.dbf file that is included with Microsoft Visual FoxPro in the Home(2)+data folder.”
“
Wildcards | Description |
The percent sign (%) | Matches one or more characters. Note that MS Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character. |
The underscore (_) | Matches one character. Note that MS Access uses a question mark (?) instead of the underscore (_) to match any one character. |
SELECT FROM table_name WHERE column LIKE ‘XXXX%’ or SELECT FROM table_name WHERE column LIKE ‘%XXXX%’ or SELECT FROM table_name WHERE column LIKE ‘XXXX_’ or SELECT FROM table_name WHERE column LIKE ‘_XXXX’ or SELECT FROM table_name WHERE column LIKE ‘_XXXX_’ |
Statement | Description |
WHERE SALARY LIKE ‘200%’ | Finds any values that start with 200 |
WHERE SALARY LIKE ‘%200%’ | Finds any values that have 200 in any position |
WHERE SALARY LIKE ‘_00%’ | Finds any values that have 00 in the second and third positions |
WHERE SALARY LIKE ‘2_%_%’ | Finds any values that start with 2 and are at least 3 characters in length |
WHERE SALARY LIKE ‘%2’ | Finds any values that end with 2 |
WHERE SALARY LIKE ‘_2%3’ | Finds any values that have a 2 in the second position and end with a 3 |
WHERE SALARY LIKE ‘2___3’ | Finds any values in a five-digit number that start with 2 and end with 3 |
“
“
SQL: LIKE Condition
This SQL tutorial explains how to use the SQL LIKE condition (to perform pattern matching) with syntax, examples, and practice exercises. Click the Try It button next to an example to test it for yourself in our SQL Editor.
Description
The SQL LIKE condition allows you to use wildcards to perform pattern matching in a query. The LIKE condition is used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.
Syntax
The syntax for the LIKE condition in SQL is:
expression LIKE pattern [ ESCAPE ‘escape_character’ ]
Wildcard | Explanation |
% | Allows you to match any string of any length (including zero length) |
_ | Allows you to match on a single character |
“
“
Wildcard | Description |
% | A substitute for zero or more characters |
_ | A substitute for a single character |
[charlist] | Sets and ranges of characters to match |
[^charlist] or [!charlist] |
SELECT * FROM Customers WHERE City LIKE ‘ber%’; SELECT * FROM Customers WHERE City LIKE ‘%es%’; SELECT * FROM Customers WHERE City LIKE ‘_erlin’; SELECT * FROM Customers WHERE City LIKE ‘L_n_on’; SELECT * FROM Customers WHERE City LIKE ‘[bsp]%’; SELECT * FROM Customers WHERE City LIKE ‘[a-c]%’; SELECT * FROM Customers WHERE City LIKE ‘[!bsp]%’; or SELECT * FROM Customers WHERE City NOT LIKE ‘[bsp]%’; |
“
sql – MySQL Wildcards * and % – Stack Overflow
【总结】
MySQL中,用于通配符的搜索:
用LIKE命令
与LIKE命令搭配使用的通配符的语法是:
与LIKE搭配的通配符语法 | 含义 | 说明 |
% | 0或多个字符 | 类似于正则(或Windows系统中Access)中的:星号* |
_ | 单个字符 | 类似于正则(或Windows系统中Access)中的:?问号 |
[AllowableCharList] | 允许的字符的列表 | |
[^NotAllowableCharList] 或 [!NotAllowableCharList] | 不允许的字符的列表 |
举例:
我此处的,希望搜索180开头的手机号,则用的就是:
SELECT * FROM users WHERE phone LIKE ‘180%’ |
转载请注明:在路上 » 【整理】MSQL中的通配符搜索:LIKE和%