This Cheat Sheet consists of several helpful tables and lists, containing information that comes up repeatedly when working with SQL. In one place, you can get a quick answer to a number of different questions that frequently arise during an SQL development effort.
SQL Criteria for Normal Forms
To ensure that database tables are designed in such a way that they will hold your data reliably, you need to be sure that they are not subject to modification anomalies. Normalizing your databases will give you that assurance. Compare the SQL criteria in the following list to the tables in your database. Doing so will alert you to the possibility of anomalies, when you find that your database is not sufficiently normalized.
First Normal Form (1NF):
- Table must be two-dimensional, with rows and columns.
- Each row contains data that pertains to one thing or one portion of a thing.
- Each column contains data for a single attribute of the thing being described.
- Each cell (intersection of row and column) of the table must be single-valued.
- All entries in a column must be of the same kind.
- Each column must have a unique name.
- No two rows may be identical.
- The order of the columns and of the rows does not matter.
Second Normal Form (2NF):
- Table must be in first normal form (1NF).
- All non-key attributes (columns) must be dependent on the entire key.
Third Normal Form (3NF):
- Table must be in second normal form (2NF).
- Table has no transitive dependencies.
Domain-Key Normal Form (DK/NF):
- Every constraint on the table is a logical consequence of the definition of keys and domains.
SQL Data Types
Here’s a list of all the formal data types that ISO/IEC standard SQL recognizes. In addition to these, you may define additional data types that are derived from these.
Exact Numerics:
- INTEGER
- SMALLINT
- BIGINT
- NUMERIC
- DECIMAL
Approximate Numerics:
- REAL
- DOUBLE PRECISION
- FLOAT
- DECFLOAT
Binary Strings:
- BINARY
- BINARY VARYING
- BINARY LARGE OBJECT
Boolean:
- BOOLEAN
Character Strings:
- CHARACTER
- CHARACTER VARYING (VARCHAR)Download and install this phone manager by clicking button below. Run it and connect iPhone to PC with attached USB cable. You can equally backup files, manage personal information, organize iOS apps, import as well as export vital records. Tap “Trust” when a pop-up window appear on your phone.Then the main information of your iPhone will show on PC. Where are voice memos stored on pc. See below how to use this tool easily:.
- CHARACTER LARGE OBJECT
- NATIONAL CHARACTER
- NATIONAL CHARACTER VARYING
- NATIONAL CHARACTER LARGE OBJECT
Datetimes:
- DATE
- TIME WITHOUT TIMEZONE
- TIMESTAMP WITHOUT TIMEZONE
- TIME WITH TIMEZONE
- TIMESTAMP WITH TIMEZONE
Intervals:
- INTERVAL DAY
- INTERVAL YEAR
Collection Types:
- ARRAY
- MULTISET
Other Types:
- ROW
- XML
SQL Value Functions
These SQL value functions perform operations on data. There are all kinds of operations that could conceivably be performed on data items, but these are some that are needed most often.
Function | Effect |
---|---|
SUBSTRING | Extracts a substring from a source string |
SUBSTRING SIMILAR | Extracts a substring from a source string, using POSIX-based regular expressions |
SUBSTRING_REGEX | Extracts from a string the first occurrence of an XQuery regular expression pattern and returns one occurrence of the matching substring |
TRANSLATE_REGEX | Extracts from a string the first or every occurrence of an XQuery regular expression pattern and replaces it or them with an XQuery replacement string |
UPPER | Converts a character string to all uppercase |
LOWER | Converts a character string to all lowercase |
TRIM | Trims off leading or trailing blanks |
TRANSLATE | Transforms a source string from one character set to another |
CONVERT | Transforms a source string from one character set to another |
Function | Effect |
---|---|
POSITION | Returns the starting position of a target string within a source string |
CHARACTER_LENGTH | Returns the number of characters in a string |
OCTET_LENGTH | Returns the number of octets (bytes) in a character string |
EXTRACT | Extracts a single field from a datetime or interval |
Function | Effect |
---|---|
CURRENT_DATE | Returns the current date |
CURRENT_TIME(p) | Returns the current time; (p) is precision of seconds |
CURRENT_TIMESTAMP(p) | Returns the current date and the current time; (p) is precision of seconds |
SQL Set Functions
The SQL set functions give you a quick answer to questions you may have about the characteristics of your data as a whole. How many rows does a table have? What is the highest value in the table? What is the lowest? These are the kinds of questions that the SQL set functions can answer for you.
Function | Effect |
COUNT | Returns the number of rows in the specified table |
MAX | Returns the maximum value that occurs in the specified able |
MIN | Returns the minimum value that occurs in the specified table |
SUM | Adds up the values in a specified column |
AVG | Returns the average of all the values in the specified column |
LISTAGG | Transforms values from a group of rows into a delimited string |
Trigonometric and Logarithmic Functions
sin
, cos
, tan
, asin
, acos
, atan
, sinh
, cosh
, tanh
, log(<base>, <value>)
, log10(<value>)
. ln( <value>)
JSON Constructor Functions
JSON_OBJECT
JSON_ARRAY
JSON_OBJECTAGG
JSON_ARRAYAGG
JSON Query Functions
JSON_EXISTS
JSON_VALUE
JSON_QUERY
JSON_TABLE
SQL WHERE Clause Predicates
Predicates boil down to either a TRUE or a FALSE result. You can filter out unwanted rows from the result of an SQL query by applying a WHERE clause whose predicate excludes the unwanted rows.
= | Equal |
<> | Not equal |
< | Less than |
<= | Less than or equal |
> | Greater than |
>= | Greater than or equal |
ALL | BETWEEN |
DISTINCT | EXISTS |
IN | LIKE |
MATCH | NOT IN |
NOT LIKE | NULL |
OVERLAPS | SOME, ANY |
UNIQUE |
A regular expression is a pattern that the regular expression engine attempts to match in input text. A pattern consists of one or more character literals, operators, or constructs. For a brief introduction, see .NET Regular Expressions.
Each section in this quick reference lists a particular category of characters, operators, and constructs that you can use to define regular expressions.
We’ve also provided this information in two formats that you can download and print for easy reference:
Download in Word (.docx) format
Download in PDF (.pdf) format
Download in PDF (.pdf) format
Character Escapes
The backslash character () in a regular expression indicates that the character that follows it either is a special character (as shown in the following table), or should be interpreted literally. For more information, see Character Escapes.
Escaped character | Description | Pattern | Matches |
---|---|---|---|
a | Matches a bell character, u0007. | a | 'u0007' in 'Error!' + 'u0007' |
b | In a character class, matches a backspace, u0008. | [b]{3,} | 'bbbb' in 'bbbb' |
t | Matches a tab, u0009. | (w+)t | 'item1t' , 'item2t' in 'item1titem2t' |
r | Matches a carriage return, u000D. (r is not equivalent to the newline character, n .) | rn(w+) | 'rnThese' in 'rnThese arentwo lines.' |
v | Matches a vertical tab, u000B. | [v]{2,} | 'vvv' in 'vvv' |
f | Matches a form feed, u000C. | [f]{2,} | 'fff' in 'fff' |
n | Matches a new line, u000A. | rn(w+) | 'rnThese' in 'rnThese arentwo lines.' |
e | Matches an escape, u001B. | e | 'x001B' in 'x001B' |
nnn | Uses octal representation to specify a character (nnn consists of two or three digits). | w040w | 'a b' , 'c d' in 'a bc d' |
x nn | Uses hexadecimal representation to specify a character (nn consists of exactly two digits). | wx20w | 'a b' , 'c d' in 'a bc d' |
c Xc x | Matches the ASCII control character that is specified by X or x, where X or x is the letter of the control character. | cC | 'x0003' in 'x0003' (Ctrl-C) |
u nnnn | Matches a Unicode character by using hexadecimal representation (exactly four digits, as represented by nnnn). | wu0020w | 'a b' , 'c d' in 'a bc d' |
| When followed by a character that is not recognized as an escaped character in this and other tables in this topic, matches that character. For example, * is the same as x2A , and . is the same as x2E . This allows the regular expression engine to disambiguate language elements (such as * or ?) and character literals (represented by * or ? ). | d+[+-x*]d+ | '2+2' and '3*9' in '(2+2) * 3*9' |
Character Classes
A character class matches any one of a set of characters. Character classes include the language elements listed in the following table. For more information, see Character Classes.
Character class | Description | Pattern | Matches |
---|---|---|---|
[ character_group] | Matches any single character in character_group. By default, the match is case-sensitive. | [ae] | 'a' in 'gray' 'a' , 'e' in 'lane' |
[^ character_group] | Negation: Matches any single character that is not in character_group. By default, characters in character_group are case-sensitive. | [^aei] | 'r' , 'g' , 'n' in 'reign' |
[ first- last] | Character range: Matches any single character in the range from first to last. | [A-Z] | 'A' , 'B' in 'AB123' |
. | Wildcard: Matches any single character except n. To match a literal period character (. or u002E ), you must precede it with the escape character (. ). | a.e | 'ave' in 'nave' 'ate' in 'water' |
p{ name} | Matches any single character in the Unicode general category or named block specified by name. | p{Lu} p{IsCyrillic} | 'C' , 'L' in 'City Lights' 'Д' , 'Ж' in 'ДЖem' |
P{ name} | Matches any single character that is not in the Unicode general category or named block specified by name. | P{Lu} P{IsCyrillic} | 'i' , 't' , 'y' in 'City' 'e' , 'm' in 'ДЖem' |
w | Matches any word character. | w | 'I' , 'D' , 'A' , '1' , '3' in 'ID A1.3' |
W | Matches any non-word character. | W | ' ' , '.' in 'ID A1.3' |
s | Matches any white-space character. | ws | 'D ' in 'ID A1.3' |
S | Matches any non-white-space character. | sS | ' _' in 'int __ctr' |
d | Matches any decimal digit. | d | '4' in '4 = IV' |
D | Matches any character other than a decimal digit. | D | ' ' , '=' , ' ' , 'I' , 'V' in '4 = IV' |
Anchors
Anchors, or atomic zero-width assertions, cause a match to succeed or fail depending on the current position in the string, but they do not cause the engine to advance through the string or consume characters. The metacharacters listed in the following table are anchors. For more information, see Anchors.
Assertion | Description | Pattern | Matches |
---|---|---|---|
^ | By default, the match must start at the beginning of the string; in multiline mode, it must start at the beginning of the line. | ^d{3} | '901' in '901-333-' |
$ | By default, the match must occur at the end of the string or before n at the end of the string; in multiline mode, it must occur before the end of the line or before n at the end of the line. | -d{3}$ | '-333' in '-901-333' |
A | The match must occur at the start of the string. | Ad{3} | '901' in '901-333-' |
Z | The match must occur at the end of the string or before n at the end of the string. | -d{3}Z | '-333' in '-901-333' |
z | The match must occur at the end of the string. | -d{3}z | '-333' in '-901-333' |
G | The match must occur at the point where the previous match ended. | G(d) | '(1)' , '(3)' , '(5)' in '(1)(3)(5)[7](9)' |
b | The match must occur on a boundary between a w (alphanumeric) and a W (nonalphanumeric) character. | bw+sw+b | 'them theme' , 'them them' in 'them theme them them' |
B | The match must not occur on a b boundary. | Bendw*b | 'ends' , 'ender' in 'end sends endure lender' |
Grouping Constructs
Grouping constructs delineate subexpressions of a regular expression and typically capture substrings of an input string. Grouping constructs include the language elements listed in the following table. For more information, see Grouping Constructs.
Grouping construct | Description | Pattern | Matches |
---|---|---|---|
( subexpression) | Captures the matched subexpression and assigns it a one-based ordinal number. | (w)1 | 'ee' in 'deep' |
(?< name> subexpression) | Captures the matched subexpression into a named group. | (?<double>w)k<double> | 'ee' in 'deep' |
(?< name1- name2> subexpression) | Defines a balancing group definition. For more information, see the 'Balancing Group Definition' section in Grouping Constructs. | (((?'Open'()[^()]*)+((?'Close-Open'))[^()]*)+)*(?(Open)(?!))$ | '((1-3)*(3-1))' in '3+2^((1-3)*(3-1))' |
(?: subexpression) | Defines a noncapturing group. | Write(?:Line)? | 'WriteLine' in 'Console.WriteLine()' 'Write' in 'Console.Write(value)' |
(?imnsx-imnsx: subexpression) | Applies or disables the specified options within subexpression. For more information, see Regular Expression Options. | Ad{2}(?i:w+)b | 'A12xl' , 'A12XL' in 'A12xl A12XL a12xl' |
(?= subexpression) | Zero-width positive lookahead assertion. | w+(?=.) | 'is' , 'ran' , and 'out' in 'He is. The dog ran. The sun is out.' |
(?! subexpression) | Zero-width negative lookahead assertion. | b(?!un)w+b | 'sure' , 'used' in 'unsure sure unity used' |
(?<= subexpression) | Zero-width positive lookbehind assertion. | (?<=19)d{2}b | '99' , '50' , '05' in '1851 1999 1950 1905 2003' |
(?<! subexpression) | Zero-width negative lookbehind assertion. | (?<!19)d{2}b | '51' , '03' in '1851 1999 1950 1905 2003' |
(?> subexpression) | Nonbacktracking (or 'greedy') subexpression. | [13579](?>A+B+) | '1ABB' , '3ABB' , and '5AB' in '1ABB 3ABBC 5AB 5AC' |
Quantifiers
A quantifier specifies how many instances of the previous element (which can be a character, a group, or a character class) must be present in the input string for a match to occur. Quantifiers include the language elements listed in the following table. For more information, see Quantifiers.
Quantifier | Description | Pattern | Matches |
---|---|---|---|
* | Matches the previous element zero or more times. | d*.d | '.0' , '19.9' , '219.9' |
+ | Matches the previous element one or more times. | 'be+' | 'bee' in 'been' , 'be' in 'bent' |
? | Matches the previous element zero or one time. | 'rai?n' | 'ran' , 'rain' |
{ n} | Matches the previous element exactly n times. | ',d{3}' | ',043' in '1,043.6' , ',876' , ',543' , and ',210' in '9,876,543,210' |
{ n,} | Matches the previous element at least n times. | 'd{2,}' | '166' , '29' , '1930' |
{ n, m} | Matches the previous element at least n times, but no more than m times. | 'd{3,5}' | '166' , '17668' '19302' in '193024' |
*? | Matches the previous element zero or more times, but as few times as possible. | d*?.d | '.0' , '19.9' , '219.9' |
+? | Matches the previous element one or more times, but as few times as possible. | 'be+?' | 'be' in 'been' , 'be' in 'bent' |
?? | Matches the previous element zero or one time, but as few times as possible. | 'rai??n' | 'ran' , 'rain' |
{ n}? | Matches the preceding element exactly n times. | ',d{3}?' | ',043' in '1,043.6' , ',876' , ',543' , and ',210' in '9,876,543,210' |
{ n,}? | Matches the previous element at least n times, but as few times as possible. | 'd{2,}?' | '166' , '29' , '1930' |
{ n, m}? | Matches the previous element between n and m times, but as few times as possible. | 'd{3,5}?' | '166' , '17668' '193' , '024' in '193024' |
Backreference Constructs
A backreference allows a previously matched subexpression to be identified subsequently in the same regular expression. The following table lists the backreference constructs supported by regular expressions in .NET. For more information, see Backreference Constructs.
Backreference construct | Description | Pattern | Matches |
---|---|---|---|
number | Backreference. Matches the value of a numbered subexpression. | (w)1 | 'ee' in 'seek' |
k< name> | Named backreference. Matches the value of a named expression. | (?<char>w)k<char> | 'ee' in 'seek' |
Alternation Constructs
Alternation constructs modify a regular expression to enable either/or matching. These constructs include the language elements listed in the following table. For more information, see Alternation Constructs.
Alternation construct | Description | Pattern | Matches |
---|---|---|---|
| | Matches any one element separated by the vertical bar (| ) character. | th(e|is|at) | 'the' , 'this' in 'this is the day.' |
(?( expression) yes| no) | Matches yes if the regular expression pattern designated by expression matches; otherwise, matches the optional no part. expression is interpreted as a zero-width assertion. | (?(A)Ad{2}b|bd{3}b) | 'A10' , '910' in 'A10 C103 910' |
(?( name) yes| no) | Matches yes if name, a named or numbered capturing group, has a match; otherwise, matches the optional no. | (?<quoted>')?(?(quoted).+?'|S+s) | 'Dogs.jpg ' , 'Yiska playing.jpg' in 'Dogs.jpg 'Yiska playing.jpg' |
Substitutions
Substitutions are regular expression language elements that are supported in replacement patterns. For more information, see Substitutions. The metacharacters listed in the following table are atomic zero-width assertions.
Character | Description | Pattern | Replacement pattern | Input string | Result string |
---|---|---|---|---|---|
$ number | Substitutes the substring matched by group number. | b(w+)(s)(w+)b | $3$2$1 | 'one two' | 'two one' |
${ name} | Substitutes the substring matched by the named group name. | b(?<word1>w+)(s)(?<word2>w+)b | ${word2} ${word1} | 'one two' | 'two one' |
$$ | Substitutes a literal '$'. | b(d+)s?USD | $$$1 | '103 USD' | '$103' |
$& | Substitutes a copy of the whole match. | $?d*.?d+ | **$&** | '$1.30' | '**$1.30**' |
$` | Substitutes all the text of the input string before the match. | B+ | $` | 'AABBCC' | 'AAAACC' |
$' | Substitutes all the text of the input string after the match. | B+ | $' | 'AABBCC' | 'AACCCC' |
$+ | Substitutes the last group that was captured. | B+(C+) | $+ | 'AABBCCDD' | 'AACCDD' |
$_ | Substitutes the entire input string. | B+ | $_ | 'AABBCC' | 'AAAABBCCCC' |
Regular Expression Options
You can specify options that control how the regular expression engine interprets a regular expression pattern. Many of these options can be specified either inline (in the regular expression pattern) or as one or more RegexOptions constants. This quick reference lists only inline options. For more information about inline and RegexOptions options, see the article Regular Expression Options.
Regular Expression Cheat Sheet Javascript
You can specify an inline option in two ways:
- By using the miscellaneous construct
(?imnsx-imnsx)
, where a minus sign (-) before an option or set of options turns those options off. For example,(?i-mn)
turns case-insensitive matching (i
) on, turns multiline mode (m
) off, and turns unnamed group captures (n
) off. The option applies to the regular expression pattern from the point at which the option is defined, and is effective either to the end of the pattern or to the point where another construct reverses the option. - By using the grouping construct
(?imnsx-imnsx:
subexpression)
, which defines options for the specified group only.
The .NET regular expression engine supports the following inline options.
Option | Description | Pattern | Matches |
---|---|---|---|
i | Use case-insensitive matching. | b(?i)a(?-i)aw+b | 'aardvark' , 'aaaAuto' in 'aardvark AAAuto aaaAuto Adam breakfast' |
m | Use multiline mode. ^ and $ match the beginning and end of a line, instead of the beginning and end of a string. | For an example, see the 'Multiline Mode' section in Regular Expression Options. | |
n | Do not capture unnamed groups. | For an example, see the 'Explicit Captures Only' section in Regular Expression Options. | |
s | Use single-line mode. | For an example, see the 'Single-line Mode' section in Regular Expression Options. | |
x | Ignore unescaped white space in the regular expression pattern. | b(?x) d+ s w+ | '1 aardvark' , '2 cats' in '1 aardvark 2 cats IV centurions' |
Miscellaneous Constructs
Miscellaneous constructs either modify a regular expression pattern or provide information about it. The following table lists the miscellaneous constructs supported by .NET. For more information, see Miscellaneous Constructs.
Construct | Definition | Example |
---|---|---|
(?imnsx-imnsx) | Sets or disables options such as case insensitivity in the middle of a pattern.For more information, see Regular Expression Options. | bA(?i)bw+b matches 'ABA' , 'Able' in 'ABA Able Act' |
(?# comment) | Inline comment. The comment ends at the first closing parenthesis. | bA(?#Matches words starting with A)w+b |
# [to end of line] | X-mode comment. The comment starts at an unescaped # and continues to the end of the line. | (?x)bAw+b#Matches words starting with A |