SQL Server Operators

SQL Server supports various operators that allow you to perform operations on data and manipulate the result set. These operators can be used in SQL queries to filter, combine, and manipulate data. Here are some commonly used SQL Server operators:

An operator is a symbol specifying an action that is performed on one or more expressions.

Following is the operator categories in SQL Server

• Arithmetic Operators
• Assignment Operators
• Compound Operators
• Bitwise Operators
• Comparison Operators
• Logical Operators
• Scope Resolution Operators
• Set Operators
• String Concatenation Operators

Arithmetic Operators:

Operator	Action
+(Add)	Addition
– (Subtract)	Subtraction
* (Multiply)	Multiplication
/ (Divide)	Division
% (Modulo)	Returns the integer remainder of a division

Assignment Operator:

The equal sign (=) is the only Transact-SQL assignment operator.

Compound Operators:

Operator	Action
+=	+= (Add Assignment)
-=	-= (Subtract Assignment)
*=	*= (Multiply Assignment)
/=	/= (Divide Assignment)
%=	%= (Modulus Assignment)
&=	&= (Bitwise And Assignment)
^=	^= (Bitwise Exclusive OR Assignment)
|=	|= (Bitwise OR Assignment)

Bitwise Operators

Comparison Operators

Operator	Meaning
= (Equals)	Equal to
> (Greater Than)	Greater than
< (Less Than)	Less than
>= (Greater than or equal to)	Greater than or equal to
<= (Less than or equal to)	Less than or equal to
<> (Not Equal to)	Not equal to
!= (Not Equal to)	Not equal to (not ISO standard)
!< (Not Less Than)	Not less than (not ISO standard)
!> (Not Greater than)	Not greater than (not ISO standard)

Logical Operators:

Operator	Meaning
All	TRUE if all of a set of comparisons are TRUE
AND	TRUE If both Boolean expressions are TRUE
ANY	TRUE if any one of a set of comparisons are TRUE.
BETWEEN	TRUE if the operand is within a range
EXISTS	TRUE if a subquery contains any rows
IN	TRUE if the operand is equal to one of a list of expressions.
LIKE	TRUE if the operand matches a pattern
NOT	Reverses the value of any other Boolean operator
OR	TRUE if either Boolean expression is TRUE.
SOME	TRUE if some of a set of comparisons are TRUE.

• There are two wildcards often used in conjunction with the LIKE operator:
•  The percent sign (%) represents zero, one, or multiple characters
• The underscore sign (_) represents one, single character.

Scope resolution operator:

The scope resolution operator provides access to static members of a compound data type.  A compound data type is one that contains multiple simple data types and methods.  Compound data types include the built-in CLR types and custom SQLCLR User-Defined Types (UDTs).

Example:  The following example shows how to use the scope resolution operator to access the GetRoot() member of the hierarchyid type.

DECLARE @hid hierarchyid;

Select @hid = hierarchyid::GetRoot();

PRINT @hid.ToString();

Here is the result set. /

Set Operators

These are used to combine the result of 2 or more Tables as a single set of values.

• Union – Union fetches all the values from the tables without Duplicates.
• Union all – Union all is same as union but it returns duplicate values too.
• Intersect – It returns the common values from the Tables.

Except – Returns any distinct values from the query left of the EXCEPT operator.  Those values return as long the right query doesn’t return those values as well.

String Concatenation Operators:

• + (String Concatenation)
• += (String Concatenation Assignment)
• % (Wildcard – Character(s) to Match)
• [] (Wildcard – Character(s) to Match)
• [^] (Wildcard – Character(s) Not to Match)
• _ (Wildcard – Match One character)

These are just a few examples of the operators available in SQL Server. Each operator has specific usage and syntax requirements, which can be used in SELECT, WHERE, JOIN, and other clauses of SQL queries. It’s important to refer to the SQL Server documentation for the specific version you are using for complete details and examples of these operators.