最新消息:20210816 当前crifan.com域名已被污染,为防止失联,请关注(页面右下角的)公众号

【整理】MSQL中的通配符搜索:LIKE和%

MySQL crifan 2863浏览 0评论

折腾:

【基本解决】Sequel Pro中设置通配符正则的过滤条件

期间,搜:

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.”

Sequel::Dataset

SQL Wildcard Operators

WildcardsDescription
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_’
StatementDescription
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

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’ ]

WildcardExplanation
%Allows you to match any string of any length (including zero length)
_Allows you to match on a single character

SQL Wildcards

WildcardDescription
% 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和%

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
89 queries in 0.170 seconds, using 22.18MB memory