SQL語法筆記

2013-09-09

SQL語法筆記

Regular Expression

MS SQL:

Select * from TABLE where ( account like '%EB% or account like '%NB%' )
Select * from TABLE where ( account like '[EN][BO][0-9][0-9][0-9][0-9][0-9][0-9]' )
select * from tb where [name]not like '%c%'

MySQL:

SELECT * FROM files WHERE origin_type REGEXP '^image'

Regular Expression Metacharacters

*

Matches zero or more instances of the string preceding it

+

Matches one or more instances of the string preceding it

?

Matches zero or one instances of the string preceding it

.

Matches any single character, except a newline

[xyz]

Matches any of x, y, or z (match one of enclosed characters)

[^xyz]

Matches any character not enclosed

[A-Z]

Matches any uppercase letter

[a-z]

Matches any lowercase letter

[0-9]

Matches any digit

^

Anchors the match from the beginning

$

Anchors the match to the end

|

Separates alternatives

{n,m}

String must occur at least n times, but not more than m times

{n}

String must occur exactly n times

{n,}

String must occur at least n times

[[:<:]]

Matches beginning of words

[[:>:]]

Matches ending of words

[:class:]

match a character class i.e.,

[:alpha:] for letters
[:space:] for whitespace
[:punct:] for punctuation
[:upper:] for upper case letters

Extras

MySQL interprets a backslash (\) character as an escape character. To use a backslash in a regular expression, you must escape it with another backslash (\\).

Whether the Regular Expression match is case sensitive or otherwise is decided by the collation method of the table. If your collation method name ends with ci then the comparison/match is case-insensitive, else if it end in cs then the match is case sensitive.