SQL LIKE子句的特殊字符列表 [英] List of special characters for SQL LIKE clause

问题描述

什么是SQL的所有特殊字符的完整列表（我感兴趣的SQL Server，但其他的也将是好的）LIKE子句？

Eg

  SELECT名称FROM Person WHERE名称LIKE'％Jon％'
  

SQL Server ：

1. ％


2. _


3. [specifier]例如[a-z]


4. [^ specifier]


5. ESCAPE子句％30！%%'ESCAPE'！'将评估30％为真实


6. '需要使用'




MySQL：



1. ％ - 任何零个或多个字符的字符串。


2. _ - 任何单个字符


3. ESCAPE子句％30！%%'ESCAPE'！'会将30％评为真实



Oracle：



1. ％ - 任何零个或多个字符的字符串。


2. _ - 任何单个字符


3. ESCAPE子句％30！%%'ESCAPE'！'将30％评为真实



Sybase >





1. ％


2. _


3. [az]


4. [^ specifier]



进度：



1. ％ - 任何零个或多个字符的字符串。



2. _ 单字符

   
   
   

   参考指南 [PDF]




PostgreSQL：



1. ％ - 任何零个或多个字符的字符串。


2. - 任何单个字符



ANSI SQL92：



1. ％


2. _


3. 只有在指定时才会显示ESCAPE字符。



PostgreSQL也有 SIMILAR TO 运算符，它添加了以下内容：



3. [specifier]


4. [^ specifier]


5. | - 两种选择之一


6. * - 重复上一个项目零次或更多次。


7. +

8. （） - 将项目组合在一起



这个想法是让这个社区维基可以成为一个一站式商店。

解决方案

对于SQL Server，请从 http ：//msdn.microsoft.com/en-us/library/ms179859.aspx ：







• ％任何

  
  
  

  WHERE title LIKE'％computer％'查找所有包含字词的书籍




• _任何单个字符。

  
  
  

  WHERE au_fname LIKE'_ean'查找以ean结尾的所有四个字母的名字（Dean，Sean等）。




• []指定范围（[af]）或集合（[abcdef]）中的任何单个字符。

  
  
  

  WHERE au_lname LIKE'[CP] arsen'查找作者以arsen结尾的姓氏，以C和P之间的任何单个字符开头，例如Carsen，Larsen，Karsen等。在范围搜索中，范围中包含的字符可能会根据排序规则的排序规则而有所不同。




• [^]不在指定范围内的任何单个字符（[^ af]）或set（[^ abcdef]）。

  
  
  

  WHERE au_lname LIKE'de [^ l]％' c $ c>所有作者姓氏以de开头，其中以下字母不是l。






What is the complete list of all special characters for a SQL (I'm interested in SQL Server but other's would be good too) LIKE clause?

E.g.

SELECT Name FROM Person WHERE Name LIKE '%Jon%'

SQL Server:

1. %
2. _
3. [specifier] E.g. [a-z]
4. [^specifier]
5. ESCAPE clause E.g. %30!%%' ESCAPE '!' will evaluate 30% as true
6. ' characters need to be escaped with ' E.g. they're becomes they''re

MySQL:

1. % - Any string of zero or more characters.
2. _ - Any single character
3. ESCAPE clause E.g. %30!%%' ESCAPE '!' will evaluate 30% as true

Oracle:

1. % - Any string of zero or more characters.
2. _ - Any single character
3. ESCAPE clause E.g. %30!%%' ESCAPE '!' will evaluate 30% as true

Sybase

1. %
2. _
3. [specifier] E.g. [a-z]
4. [^specifier]

Progress:

1. % - Any string of zero or more characters.

2. _ - Any single character

   Reference Guide here [PDF]

PostgreSQL:

1. % - Any string of zero or more characters.
2. _ - Any single character

ANSI SQL92:

1. %
2. _
3. An ESCAPE character only if specified.

PostgreSQL also has the SIMILAR TO operator which adds the following:

3. [specifier]
4. [^specifier]
5. | - either of two alternatives
6. * - repetition of the previous item zero or more times.
7. + - repetition of the previous item one or more times.
8. () - group items together

The idea is to make this a community Wiki that can become a "One stop shop" for this.

解决方案

For SQL Server, from http://msdn.microsoft.com/en-us/library/ms179859.aspx :

• % Any string of zero or more characters.

  WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.

• _ Any single character.

  WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).

• [ ] Any single character within the specified range ([a-f]) or set ([abcdef]).

  WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. In range searches, the characters included in the range may vary depending on the sorting rules of the collation.

• [^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

  WHERE au_lname LIKE 'de[^l]%' all author last names starting with de and where the following letter is not l.

这篇关于SQL LIKE子句的特殊字符列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！