T-SQL search criteria

search condition

It is a combination of one or more predicates using logical operators and NOT.

grammar

:: = {[NOT] | ()} [{and |}} [not] { | ()}]} [, ... n]

:: = {Expression {= | <> |! = |> | "" "! <} express [not] Like string_expression [escape 'escape_character'] | Expression [ NOT] BETWEEN expression AND expression | expression IS [NOT] NULL | CONTAINS ({column | *}, '') | FREETEXT ({column | *}, 'freetext_string') | expression [NOT] IN (subquery | Expression [, ... n]) | express {= | <> |! = |> | <= |! <} {all | some | any} (SUBQUERY) | }

parameter

Specifies the condition of the line returned in the result set of the SELECT statement, query the result set of the expression or subquery. For the Update statement, specify the row to update. For the DELETE statement, specify the row to delete. There is no limit to the number of predicates that can be included in the Transact-SQL statement search criteria.

NOT

For the Boolean expression specified by the predicate. For more information, see

NOT.

AND

The value is TRUE when two conditions are True in combination. For more information, see

AND.

Oral

The value is TRUE when one condition is combined and the value is TRUE. For more information, see

OR.

Is the expression that returns true, false or unknown.

EXPRESSION

It is column name, constant, function, variable, scalar query, or any combination of column names, constants, and functions connected by operator or subquery. This expression can also include a CASE function.

=

It is an operator for testing whether two expressions are equal.

<>

It is an operator for testing the conditions of two expressions that are not equal to each other.

! =

It is an operator for testing the conditions of two expressions that are not equal to each other.

>

It is an operator for testing a condition greater than another condition.

> =

Is an operator for testing a condition greater than or equal to another.

!>

It is an operator for testing a condition that is not greater than another.

<

It is an operator for testing a condition that the expression is less than the other.

<=

Is an operator for testing a condition that is less than or equal to another. ! <

It is an operator for testing a condition that is not less than another.

String_expression

It is a string and wildcard.

[NOT] LIKE

Indicates that the subsequent string usage pattern matches. For more information, see

Like.

Escape

'escape_

CHARACTER '

Allows searching in a string instead of using it as a wildcard.

Escape_character is a character that represents this special purpose before the wildcard.

[NOT] BETWEEN

The specified value is included. Separate the start value and the end value using the AND. For more information, see

Between.

IS [not] null

Specify a search for null or non-null values ​​based on the keyword used. If any operand is NULL, the expression of the operator or the arithmetic operator will take the value null.

Contains

In columns that contain characters based data, search for exact or "fuzzy" (inaccurate) matching items of single words and phrases, approximate words within a certain spacing, and weighted matching. Can only be used with the SELECT statement. For more information, see

Contains.

Fretext

By searching with a value of the character-based data, a simple form of natural language query is provided. Can only be used with the SELECT statement. For more information, see

Fretext.

[Not] in

Depending on the expression or excluded in the list or excluded in the list, specify a search for the expression. Search expressions can be constant or column names, and the list can be a set of constants or more as a subquery. Place the list of values ​​in parentheses. For more information, see

IN.

SubQuery

Can be seen as a restricted SELECT statement and similar to

SELECT.

All

Working with the comparative operator and subquery. If all the values ​​retrieved by the subquery, returns true if it is not all the comparison operations or sub-queries do not return to the external statement if not all values ​​are not returned to the external statement. For more information, see

ALL.

{Some | any}

Working with the comparative operator and subquery. If any of the subqueries retrieved satisfies the comparison operation, returns true if there is no value in the subquery that satisfies the comparison or subquerix does not return to the external statement, then return false. Otherwise, the expression is unknown. For more information, see

Some | Any.

Exists

Whether to use the subquery to test whether the line returned by subqueries exists. For more information, see

EXISTS.

Comment

The priority order of the logical operator is NOT (highest), followed by AND, and finally OR. The order in the same priority is from left to right. In the search criteria, the parentheses can be used instead of this order. For more information on how logical operators operate on real values, see AND, OR and NOT.

Example

A. Use the LIKE and ESCAPE syntax in WHERE

The following example assumes that the Description is included in the Finances table. To search for the description column that contains the exact character G_, use the Escape option because _ is a wildcard. If you do not specify an Escape option, the query will search for any description of any single character outside of the letter G follow-up.

SELECT *

From Finances

WHERE DESCRIPTION LIKE 'GS_' Escape 'S'GO

B. Using WHERE and LIKE syntax for UNICODE data

The following example uses the WHERE clause to search the company name, the contact name, phone and fax number of the company containing the string Snabköp.

Use northwind

Select CompanyName, ContactName, Phone, Fax

From customer

WHERE COMPANYNAME LIKE N '% Snabbköp'

Order by CompanyName ASC, ContactName ASC