Close Window

Statements

IF...THEN...ELSE...END

Imposes conditions on the execution of a formula. The formula following a THEN keyword is executed if the condition is satisfied (when the Boolean expression returns TRUE). The optional ELSE keyword introduces an alternate formula that is executed when the IF condition is not satisfied (when the Boolean expression returns FALSE).

Syntax
IF Boolean_expression THEN
formula_1
[ ELSE
formula_2 ]
END
Remarks
IF...THEN...ELSE...END statements can NOT be nested. If you need to compose statement with several conditions use searched CASE WHEN... THEN...ELSE...END statement
Examples:
IF( [Country]='Canada' OR [Country]='United States' ) THEN
[Country] + ' is in North America'
ELSE
'International'
END

CASE...WHEN...THEN...ELSE...END

Evaluates a list of conditions and returns one of multiple possible result expressions.
CASE has two formats:
The simple CASE statement compares an expression to a set of simple expressions to determine the result.

The searched CASE statement evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

Syntax
Simple CASE statement:
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Searched CASE statement:
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
Arguments
input_expression
Is the expression evaluated when using the simple CASE format. input_expression is any valid expression.
WHEN when_expression
Is a simple expression to which input_expression is compared when using the simple CASE format. when_expression is any valid expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.
n
Is a placeholder indicating that multiple WHEN when_expression THEN result_expression clauses, or multiple WHEN Boolean_expression THEN result_expression clauses can be used.
THEN result_expression
Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression is any valid expression.
ELSE else_result_expression
Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.
WHEN Boolean_expression
Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.
Result Types
Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.
Result Values
Simple CASE statement:
Evaluates input_expression, and then, in the order specified, evaluates input_expression = when_expression for each WHEN clause.

Returns the result_expression of the first (input_expression = when_expression) that evaluates to TRUE.
If no input_expression = when_expression evaluates to TRUE, CASE returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

Searched CASE statement:
Evaluates, in the order specified, Boolean_expression for each WHEN clause.

Returns result_expression of the first Boolean_expression that evaluates to TRUE.
If no Boolean_expression evaluates to TRUE, CASE returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

Examples

A. Use a simple CASE statement
CASE( [Country] )
WHEN 'United States' THEN 'Domestic'
WHEN 'Canada' THEN 'North America'
WHEN 'Mexico' THEN 'North America'
ELSE 'International'
END

B. Use a simple CASE statement
CASE [State]
WHEN 'NY' THEN ' State is New York'
WHEN 'NJ' THEN ' State is New Jersey'
WHEN 'NY' THEN ' State is New York'
WHEN 'NV' THEN ' State is Nevada'
ELSE 'State is ' + [State]
END

C. Use a searched CASE statement
CASE
WHEN UPPER([First Name]) = 'CHERYL' THEN 'Contact ' + [Contact] + ', type 1'
WHEN SUBSTRING([Last Name],1,1) = 'J' THEN 'Contact ' + [Contact] + ', type 2'
WHEN ([First Name] = 'Sean')AND([State] = 'NY')THEN 'Contact ' + [Contact] + ', type 3'
ELSE 'Unsupported contact type'
END

Functions

ABS
Returns the absolute, positive value of the given numeric expression.

Syntax
ABS ( numeric_expression )
Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category.
Return Types
Returns the same type as numeric_expression.
Examples
ABS( [NumericField] )
ABS( -1.0 ) -- the result is: 1.0
ABS( 0.0 ) -- the result is: 0.0
ABS( 1.0 ) -- the result is: 1.0

ACOS
Returns the angle, in radians, whose cosine is the given float expression; also called arccosine.
Syntax
ACOS ( I )
Arguments
float_expression
Is an expression of the type float or real, with a value from -1 through 1. Values outside this range return NULL.
Return Types
float
Examples
ACOS( [NumericField] )
ACOS(-1) -- the result is: 3.14159

ASCII
Returns the ASCII code value of the leftmost character of a character expression.
Syntax
ASCII ( character_expression )
Arguments
character_expression
Is an expression of the type char or varchar.
Return Types
int
Examples
ASCII( [StringField] )
ASCII('test') -- the result is: 116

ASIN
Returns the angle, in radians, whose sine is the given float expression (also called arcsine).
Syntax
ASIN ( float_expression )
Arguments
float_expression
Is an expression of the type float, with a value from -1 through 1. Values outside this range return NULL.
Return Types
float
Examples
ASIN( [FloatField] )
ASIN(-1) -- the result is: -1.5708

ATAN
Returns the angle in radians whose tangent is the given float expression (also called arctangent).
Syntax
ATAN ( float_expression )
Arguments
float_expression
Is an expression of the type float.
Return Types
float
Examples
ATAN( [FloatField] )
ATAN(-45.01) -- the result is: -1.54858

ATN2
Returns the angle, in radians, whose tangent is between the two given float expressions (also called arctangent).
Syntax
ATN2 ( float_expression , float_expression )
Arguments
float_expression
Is an expression of the float data type.
Return Types
float
Examples
ATN2( [FloatField1], [FloatField2] )
ATN2( 35.175643, 129.44 ) -- the result is: 0.265345

CAST
Explicitly converts an expression of one data type to another.
Syntax
CAST ( expression AS data_type )
Arguments
expression
Is an expression to convert.
data_type
Is the target data type.
Examples
CAST( SQUARE([Field1]) as int )
'The price is ' + CAST( [NumericField] as varchar(12) )

CEILING
Returns the smallest integer greater than, or equal to, the given numeric expression.
Syntax
CEILING ( numeric_expression )
Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category.
Return Types
Returns the same type as numeric_expression.
Examples
CEILING([NumericField])
CEILING( 123.45) -- the result is: 124
CEILING(-123.45) -- the result is: -123
CEILING(0.0) -- the result is: 0

CHAR
A string function that converts an int ASCII code to a character.
Syntax
CHAR ( integer_expression )
Arguments
integer_expression
Is an integer from 0 through 255. NULL is returned if the integer expression is not in this range.
Return Types
char(1)
Remarks
CHAR can be used to insert control characters into character strings. The table shows some commonly used control characters.

Examples
CHAR( 78 ) -- the result is: N
[Contact] + CHAR(13) + [Address 1] + CHAR(13) + [City]

/*
the result is:
Chris Huffman
13 East 54th St.
New York
*/

CHARINDEX
Returns the starting position of the specified expression in a character string.
Syntax
CHARINDEX ( expression1 , expression2 [ , start_location ] )
Arguments
expression1
Is an expression containing the sequence of characters to be found.
expression2
Is an expression, usually a field searched for the specified sequence. expression2 is of the character string data type category.
start_location
Is the character position to start searching for expression1 in expression2. If start_location is not given, is a negative number, or is zero, the search starts at the beginning of expression2.
Return Types
int
Remarks
If either expression1 or expression2 is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type.
If either expression1 or expression2 is NULL, CHARINDEX returns NULL.
If expression1 is not found within expression2, CHARINDEX returns 0.
Examples
CHARINDEX( 'Huf', [Contact] ) -- the result is 7 for contact 'Chris Huffman'

CONVERT
Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.
Syntax
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Arguments
expression
Is an expression to convert.
data_type
Is the target data type.
length
Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.
style
Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).
In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

* The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).
** Input when converting to datetime; output when converting to character data.
*** Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the table. For conversion from float, money, or smallmoney to character data, the output is equivalent to style 2. For conversion from real to character data, the output is equivalent to style 1.

When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when converting from datetime or smalldatetime values by using an appropriate char or varchar data type length.
This table shows the style values for float or real conversion to character data.

In the following table, the column on the left represents the style value for money or smallmoney conversion to character data.

Return Types
Returns the same value as data type 0.
Remarks
Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST (CONVERT) function to be specified. This chart shows all explicit and implicit data type conversions allowed.

View conversations table

Note: Because Unicode data always uses an even number of bytes, use caution when converting binary or varbinary to or from Unicode supported data types. For example, this conversion does not return a hexadecimal value of 41, but of 4100:
Examples
-- converts Birth Date field to string using style 113 (Europe default + milliseconds )
CONVERT ( varchar(64), [Birth Date] , 113 )
--result is '04 Apr 1964 20:00:00:000' for contact 'Chris Huffman'

COS
A mathematic function that returns the trigonometric cosine of the given angle (in radians) in the given expression.
Syntax
COS ( float_expression )
Arguments
float_expression
Is an expression of type float.
Return Types
float
Examples
COS( [NumericField] )
COS(14.78) -- result is: -0.599465

COT
A mathematic function that returns the trigonometric cotangent of the specified angle (in radians) in the given float expression.
Syntax
COT ( float_expression )
Arguments
float_expression
Is an expression of type float.
Return Types
float
Examples
COT( [NumericField] )
COT(124.1332) -- result is: -0.040312

DATEADD
Returns a new datetime value based on adding an interval to the specified date.
Syntax
DATEADD ( datepart , number, date )
Arguments
datepart
Is the parameter that specifies on which part of the date to return a new value. The table lists the dateparts and abbreviations.

number
Is the value used to increment datepart. If you specify a value that is not an integer, the fractional part of the value is discarded. For example, if you specify day for datepart and 1.75 for number, date is incremented by 1.
date
Is an expression that returns a datetime or smalldatetime value, or a character string in a date format.
Return Types
Returns datetime, but smalldatetime if the date argument is smalldatetime.
Examples
DATEADD( day, 10, [Birth Date] ) -- add 10 days to 'Birth Date' field

DATEDIFF
Returns the number of date and time boundaries crossed between two specified dates.
Syntax
DATEDIFF ( datepart , startdate , enddate )
Arguments
datepart
Is the parameter that specifies on which part of the date to calculate the difference. The table lists dateparts and abbreviations.

startdate
Is the beginning date for the calculation. startdate is an expression that returns a datetime or smalldatetime value, or a character string in a date format.
Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.
enddate
Is the ending date for the calculation. enddate is an expression that returns a datetime or smalldatetime value, or a character string in a date format.
Return Types
integer
Remarks
startdate is subtracted from enddate. If startdate is later than enddate, a negative value is returned.
DATEDIFF produces an error if the result is out of range for integer values. For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number is 68 years.
The method of counting crossed boundaries such as minutes, seconds, and milliseconds makes the result given by DATEDIFF consistent across all data types. The result is a signed integer value equal to the number of datepart boundaries crossed between the first and second date. For example, the number of weeks between Sunday, January 4, and Sunday, January 11, is 1.
Examples
DATEDIFF( year, [Birth Date], getdate() ) -- calculate age in years
DATEDIFF( month,[Birth Date], getdate() ) % 12 -- and months
DATEDIFF( month,[Birth Date], getdate() ) / 12 -- the same as 1st line( calculate age in years )

DATENAME
Returns a character string representing the specified datepart of the specified date.
Syntax
DATENAME ( datepart , date )
Arguments
datepart
Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations.

The weekday (dw) datepart returns the day of the week (Sunday, Monday, and so on).
Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type for dates after January 1, 1753. Store as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.
Return Types
nvarchar
Examples
DATENAME( month, [Birth Date] ) -- result is 'April' for contact 'Chris Huffman'

DATEPART
Returns an integer representing the specified datepart of the specified date.
Syntax
DATEPART ( datepart , date )
Arguments
datepart
Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations.

The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, zzzz') = 1, where zzzz is any year.
The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.
date
Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753. Store dates as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.
If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.
Return Types
int
Remarks
The DAY, MONTH, and YEAR functions are synonyms for DATEPART(dd, date), DATEPART(mm, date), and DATEPART(yy, date), respectively.
Examples
DATEPART( month, [Birth Date] ) -- result is 4 for contact 'Chris Huffman'

DateToStr
Explicitly converts an datetime expression to string in USA format 'MM/DD/YYYY'
Syntax
DateToStr ( datetime_expression )
Arguments
datetime_expression
Is an expression to convert.
Examples
DateToStr( [Birth Date] )

DateToString
Explicitly converts an datetime expression to string using specified format style.
Syntax
DateToString ( datetime_expression, style )
Arguments
datetime_expression
Is an expression to convert.
style
Is the style of date format ( see CONVERT function description for style details )
Examples
DateToString( [Birth Date], 104 ) - convert to Germat format dd.mm.yy

DAY
Returns an integer representing the day datepart of the specified date.
Syntax
DAY ( date )
Arguments
date
Is an expression of type datetime or smalldatetime.
Return Type
int
Remarks
This function is equivalent to DATEPART(dd, date).
Examples
DAY('03/12/1998') -- result is 12

DEGREES
Given an angle in radians, returns the corresponding angle in degrees.
Syntax
DEGREES ( numeric_expression )
Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category.
Return Code Values
Returns the same type as numeric_expression.
Examples
DEGREES( PI()/2 ) -- result is 90 ( the number of degrees in PI/2 radians is 90 )

DIFFERENCE
Returns the difference between the SOUNDEX values of two character expressions as an integer.
Syntax
DIFFERENCE ( character_expression , character_expression )
Arguments
character_expression
Is an expression of type char or varchar.
Return Types
int
Remarks
The integer returned is the number of characters in the SOUNDEX values that are the same. The return value ranges from 0 through 4, with 4 indicating the SOUNDEX values are identical.
Examples
In the first part of this example, the SOUNDEX values of two very similar strings are compared, and DIFFERENCE returns a value of 4. In the second part of this example, the SOUNDEX values for two very different strings are compared, and DIFFERENCE returns a value of 0.
DIFFERENCE('Green','Greene') -- result is 4
DIFFERENCE('Blotchet-Halls', 'Greene') -- result is 0
See Also
SOUNDEX function

EXP
Returns the exponential value of the given float expression.
Syntax
EXP ( float_expression )
Arguments
float_expression
Is an expression of type float.
Return Types
float
Examples
CAST( ROUND(EXP([NummericField]),0) as INT ) -- calculate EXP, ROUND result and convert it to INT
ROUND( EXP(4),0 ) -- result is 55.0

FLOOR
Returns the largest integer less than or equal to the given numeric expression.
Syntax
FLOOR ( numeric_expression )
Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category.
Return Types
Returns the same type as numeric_expression.
Examples
FLOOR([NumericField])
FLOOR(123.45) -- result is 123
FLOOR(-123.45) -- result is -124

GETDATE
Returns the current system date and time.
Syntax
GETDATE ( )
Return Types
datetime
Examples
GETDATE()

GETUTCDATE
Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer.
Syntax
GETUTCDATE()
Return Types
datetime

LEFT
Returns the part of a character string starting at a specified number of characters from the left.
Syntax
LEFT ( character_expression , integer_expression )
Arguments
character_expression
Is an expression of character or binary data. character_expression can be a constant, variable, or field. character_expression must be of a data type that can be implicitly convertible to varchar. Otherwise, use the CAST function to explicitly convert character_expression.
integer_expression
Is a positive whole number. If integer_expression is negative, a null string is returned.
Return Types
varchar
Examples
LEFT( [Contact], 5 ) -- result is 'Chris' for contact 'Chris Huffman'
LEFT( 'abcdefg', 2 ) -- result is 'ab'

LEN
Returns the number of characters of the given string expression, excluding trailing blanks.
Syntax
LEN ( string_expression )
Arguments
string_expression
Is the string expression to be evaluated.
Return Types
int
Examples
LEN( [Contact] ) -- result is 13 for contact 'Chris Huffman'
LEN( 'abcdefg' ) -- result is 7

LOG
Returns the natural logarithm of the given float expression.
Syntax
LOG ( float_expression )
Arguments
float_expression
Is an expression of the float data type.
Return Types
float
Examples
LOG(5.175643) -- result is 1.64396
CAST( ROUND( LOG([NumericField]), 0 ) as INT ) -- calculate LOG, ROUND result and convert it to INT

LOG10
Returns the base-10 logarithm of the given float expression.
Syntax
LOG10 ( float_expression )
Arguments
float_expression
Is an expression of the float data type.
Return Types
float
Examples
LOG10(5.175643) -- result is 0.7139
CAST( ROUND( LOG10([NumericField]), 0 ) as INT ) -- calculate LOG10, ROUND result and convert it to INT

LOWER
Returns a character expression after converting uppercase character data to lowercase.
Syntax
LOWER ( character_expression )
Arguments
character_expression
Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.
Return Types
varchar
Examples
LOWER( [Contact] ) -- result is 'chris huffman' for contact 'Chris Huffman'
LOWER( 'AbcDefG' ) -- result is 'abcdefg'

LTRIM
Returns a character expression after removing leading blanks.
Syntax
LTRIM ( character_expression )
Arguments
character_expression
Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.
Return Type
varchar
Examples
LTRIM( ' abcdefg' ) -- result is 'abcdefg'
LTRIM( [StringField] )

MAX
Returns maximum from 2 numeric values.
Syntax
MAX ( numeric_expression1, numeric_expression2 )
Examples
MAX( [NumericField1], [NumericField2] )
MAX( 12, 76 ) -- the result is: 76
MAX( 7.6, 7.9 ) -- the result is: 7.9

MIN
Returns minimum from 2 numeric values.
Syntax
MIN ( numeric_expression1, numeric_expression2 )
Examples
MIN( [NumericField1], [NumericField2] )
MIN( 12, 76 ) -- the result is: 12
MIN( 7.6, 7.9 ) -- the result is: 7.6

MONTH
Returns an integer that represents the month part of a specified date.
Syntax
MONTH ( date )
Arguments
date
Is an expression returning a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753.
Return Types
int
Remarks
MONTH is equivalent to DATEPART(mm, date).
Always enclose datetime values in quotation marks. For earlier dates, store dates as character data.
Examples
MONTH( [Birth Date] ) -- result is

NCHAR
Returns the Unicode character with the given integer code, as defined by the Unicode standard.
Syntax
NCHAR ( integer_expression )
Arguments
integer_expression
Is a positive whole number from 0 through 65535. If a value outside this range is specified, NULL is returned.
Return Types
nchar(1)
Examples
NCHAR(101) -- result is 'e'

PATINDEX
Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
Syntax
PATINDEX ( '%pattern%' , expression )
Arguments
pattern
Is a literal string. Wildcard characters can be used; however, the % character must precede and follow pattern (except when searching for first or last characters). pattern is an expression of the short character data type category.
expression
Is an expression, usually a field that is searched for the specified pattern. expression is of the character string data type category.
Return Types
int
Remarks
If either pattern or expression is NULL, PATINDEX returns NULL.
Examples
PATINDEX('%huffm_n%', [Contact] )-- result is 7 for contact 'Chris Huffman' ('_' sign is used as wildcard)

PI
Returns the constant value of PI.
Syntax
PI ( )
Return Types
float
Examples
PI()

POWER
Returns the value of the given expression to the specified power.
Syntax
POWER ( numeric_expression , y )
Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category.
y
Is the power to which to raise numeric_expression. y can be an expression of the exact numeric or approximate numeric data type category.
Return Types
Same as numeric_expression.
Examples
POWER(2, 4) -- result is 16 ( 2 in the power 4 )
CAST( POWER([NumericField],4) as INT )

RADIANS
Returns radians when a numeric expression, in degrees, is entered.
Syntax
RADIANS ( numeric_expression )
Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category.
Return Types
Returns the same type as numeric_expression.
Examples
CAST( ROUND(RADIANS([NummericField]),0) as INT ) -- calculate RADIANS, ROUND result and convert it to INT
RADIANS(-45.01) -- result is -0.7855

RAND
Returns a random float value from 0 through 1.
Syntax
RAND ( [ seed ] )
Arguments
seed
Is an integer expression that gives the seed or start value.
Return Types
float
Examples
RAND(1)

REPLACE
Replaces all occurrences of the second given string expression in the first string expression with a third expression.
Syntax
REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )
Arguments
'string_expression1'
Is the string expression to be searched. string_expression1 can be of character or binary data.
'string_expression2'
Is the string expression to try to find. string_expression2 can be of character or binary data.
'string_expression3'
Is the replacement string expression string_expression3 can be of character or binary data.
Return Types
Returns character data if string_expression (1, 2, or 3) is one of the supported character data types. Returns binary data if string_expression (1, 2, or 3) is one of the supported binary data types.
Examples
--This example replaces the string cde in abcdefghi with zzz.
REPLACE( 'abcdefghicde', 'cde', 'zzz' ) -- result is abzzzfghizzz
REPLACE( [Contact] , 'Chris', 'C.' ) -- result is 'C. Huffman' for contact 'Chris Huffman'

REPLICATE
Repeats a character expression for a specified number of times.
Syntax
REPLICATE ( character_expression , integer_expression )
Arguments
character_expression
Is an alphanumeric expression of character data. character_expression can be a constant, variable, or field of either character or binary data.
integer_expression
Is a positive whole number. If integer_expression is negative, a null string is returned.
Return Types
varchar
character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use the CAST function to convert explicitly character_expression.
Examples
REPLICATE( [Contact] ,2 ) -- result is 'Chris HuffmanChris Huffman' for contact 'Chris Huffman'
REPLICATE( 'Abc' ,3 ) -- result is 'AbcAbcAbc'

REVERSE
Returns the reverse of a character expression.
Syntax
REVERSE ( character_expression )
Arguments
character_expression
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.
Return Types
varchar
Remarks
character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.
Examples
REVERSE( 'Abc' ) -- result is 'cbA'
REVERSE( [Contact] ) -- result is 'namffuH sirhC' for contact 'Chris Huffman'

RIGHT
Returns the part of a character string starting a specified number of integer_expression characters from the right.
Syntax
RIGHT ( character_expression , integer_expression )
Arguments
character_expression
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.
integer_expression
Is the starting position, expressed as a positive whole number. If integer_expression is negative, an error is returned.
Return Types
varchar
character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.
Examples
RIGHT( [Contact], 7 ) -- result is 'Huffman' for contact 'Chris Huffman'
RIGHT( 'Abc', 2 ) -- result is 'bc'

ROUND
Returns a numeric expression, rounded to the specified length or precision.
Syntax
ROUND ( numeric_expression , length [ , function ] )
Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category.
length
Is the precision to which numeric_expression is to be rounded. When length is a positive number, numeric_expression is rounded to the number of decimal places specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.
function
Is the type of operation to perform. function must be int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.
Return Types
Returns the same type as numeric_expression.
Remarks
ROUND always returns a value. If length is negative and larger than the number of digits before the decimal point, ROUND returns 0.

ROUND returns a rounded numeric_expression, regardless of data type, when length is a negative number.

Examples
This example shows two expressions illustrating that with the ROUND function the last digit is always an estimate.
ROUND(123.9994, 3) -- result is 123.9990
ROUND(123.9995, 3) -- result is 124.0000

B. Use ROUND and rounding approximations
This example shows rounding and approximations.
ROUND(123.4545, 2) -- result is 123.4500
ROUND(123.45, -2) -- result is 100.00
C. Use ROUND to truncate
This example demonstrates the difference between rounding and truncation. The first statement rounds the result. The second statement truncates the result.
ROUND(150.75, 0) -- result is 151.00
ROUND(150.75, 0, 1) -- result is 150.00

RTRIM
Returns a character string after truncating all trailing blanks.
Syntax
RTRIM ( character_expression )
Arguments
character_expression
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.
Return Types
varchar
Remarks
character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use the CAST function to explicitly convert character_expression.
Examples
RTRIM( 'abcdefg ' ) -- result is 'abcdefg'
RTRIM( [StringField] )

SIGN
Returns the positive (+1), zero (0), or negative (-1) sign of the given expression.
Syntax
SIGN ( numeric_expression )
Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.
Return Types
float
Examples
SIGN( [NummericField] )
SIGN( 123 ) -- result is 1
SIGN( -123 ) -- result is -1
SIGN( 0 ) -- result is 0

SIN
Returns the trigonometric sine of the given angle (in radians) in an approximate numeric (float) expression.
Syntax
SIN ( float_expression )
Arguments
float_expression
Is an expression of type float.
Return Types
float
Examples
SIN( [NummericField] )
SIN( 45.175643 ) -- result is 0.929607

SOUNDEX
Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.
Syntax
SOUNDEX ( character_expression )
Arguments
character_expression
Is an alphanumeric expression of character data. character_expression can be a constant, variable, or column.
Return Types
char
Remarks
SOUNDEX converts an alpha string to a four-character code to find similar-sounding words or names. The first character of the code is the first character of character_expression and the second through fourth characters of the code are numbers. Vowels in character_expression are ignored unless they are the first letter of the string. String functions can be nested.
Examples
SOUNDEX ('Smith') -- result is 'S530'

SPACE
Returns a string of repeated spaces.
Syntax
SPACE ( integer_expression )
Arguments
integer_expression
Is a positive integer that indicates the number of spaces. If integer_expression is negative, a null string is returned.
Return Types
char
Remarks
To include spaces in Unicode data, use REPLICATE instead of SPACE.
Examples
'Contact name:' + SPACE( 2 ) + [Contact]
-- result is 'Contact name: Chris Huffman' for contact 'Chris Huffman'

SQRT
Returns the square root of the given expression.
Syntax
SQRT ( float_expression )
Arguments
float_expression
Is an expression of type float.
Return Types
float
Examples
SQRT(4) -- result is 2.0
CAST( ROUND( SQRT( [NummericField]),0,0) as int ) -- calculate SQRT, ROUND result and convert it to INT

SQUARE
Returns the square of the given expression.
Syntax
SQUARE ( float_expression )
Arguments
float_expression
Is an expression of type float.
Return Types
float
Examples
SQUARE(2) -- result is 4.0
CAST( ROUND( SQUARE( [NummericField]),0,0) as int ) -- calculate SQUARE, ROUND result and convert it to INT

STR
Returns character data converted from numeric data.
Syntax
STR ( float_expression [ , length [ , decimal ] ] )
Arguments
float_expression
Is an expression of approximate numeric (float) data type with a decimal point.
length
Is the total length, including decimal point, sign, digits, and spaces. The default is 10.
decimal
Is the number of places to the right of the decimal point.
Return Types
char
Remarks
If supplied, the values for length and decimal parameters to STR should be positive. The number is rounded to an integer by default or if the decimal parameter is 0. The specified length should be greater than or equal to the part of the number before the decimal point plus the number's sign (if any). A short float_expression is right-justified in the specified length, and a long float_expression is truncated to the specified number of decimal places. For example, STR(12,10) yields the result of 12, which is right-justified in the result set. However, STR(1223, 2) truncates the result set to **. String functions can be nested.
Note: To convert to Unicode data, use STR inside a CONVERT or CAST conversion function.
Examples
STR([NummericField])
A. Use STR
This example converts an expression consisting of five digits and a decimal point to a six-position character string. The fractional part of the number is rounded to one decimal place.
STR(123.45, 6, 1) -- result is '123.5'

When the expression exceeds the specified length, the string returns ** for the specified length.
STR(123.45, 2, 2) -- result is '**'

Even when numeric data is nested within STR, the result is character data with the specified format.
STR (FLOOR (123.45), 8, 3)-- result is '123.000'

B. Use the STR and CONVERT functions
This example compares the results of STR and CONVERT.
STR(3.147) -- result is '3'
STR(3.147, 5, 2) -- result is '3.15'
STR(3.147, 5, 3) -- result is '3.147'

STUFF
Deletes a specified length of characters and inserts another set of characters at a specified starting point.
Syntax
STUFF ( character_expression , start , length , character_expression )
Arguments
character_expression
Is an expression of character data. character_expression can be a constant, variable, or field of either character or binary data.
start
Is an integer value that specifies the location to begin deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned.
length
Is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression.
Return Types
Returns character data if character_expression is one of the supported character data types. Returns binary data if character_expression is one of the supported binary data types.
Remarks
String functions can be nested.
Examples
This example returns a character string created by deleting three characters from the first string (abcdef) starting at position 2 (at b) and inserting the second string at the deletion point.
STUFF('abcdef', 2, 3, 'ijklmn') -- result is 'aijklmnef'
STUFF( [Contact] , 2, 5, '.') -- result is 'C.Huffman' for contact 'Chris Huffman'

SUBSTRING
Returns part of a character, binary, text, or image expression.
Syntax
SUBSTRING ( expression , start , length )
Arguments
expression
Is a character string, binary string, text, image, a field, or an expression that includes a field.
start
Is an integer that specifies where the substring begins.
length
Is an integer that specifies the length of the substring (the number of characters or bytes to return).
Examples
SUBSTRING( 'abcdefg', 3, 2 ) -- result is 'cd'
SUBSTRING( [Contact] , 1, 5 ) -- result is 'Chris' for contact 'Chris Huffman'

TAN
Returns the tangent of the input expression.
Syntax
TAN ( float_expression )
Arguments
float_expression
Is an expression of type float or real, interpreted as number of radians.
Return Types
float
Examples
TAN( PI()/2 ) -- result is 1.63318e+016
TAN( [NumericField] )

ToStr
Explicitly converts an numeric expression to string.
Syntax
ToStr ( numeric_expression )
Arguments
numeric_expression
Is an expression to convert.
Examples
ToStr( [NumericField] )
ToStr( 123.7 )

ToInt
Explicitly converts an float expression to integer.
Syntax
ToInt ( float_expression )
Arguments
float_expression
Is an expression to convert.
Examples
ToInt( [FloatField] )
ToInt( 123.7 ) - result is 123

UNICODE
Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.
Syntax
UNICODE ( 'ncharacter_expression' )
Arguments
'ncharacter_expression'
Is an nchar or nvarchar expression.
Return Types
int
Examples
UNICODE( N'Hello' ) -- result is 72

UPPER
Returns a character expression with lowercase character data converted to uppercase.
Syntax
UPPER ( character_expression )
Arguments
character_expression
Is an expression of character data. character_expression can be a constant, variable, or field of either character or binary data.
Return Types
varchar
Remarks
character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use the CAST function to explicitly convert character_expression.
Examples
UPPER ('Abc') -- result is 'ABC'
UPPER ( [Contact] ) -- result is 'CHRIS HUFFMAN' for contact 'Chris Huffman'

YEAR
Returns an integer that represents the year part of a specified date.
Syntax
YEAR ( date )
Arguments
date
An expression of type datetime or smalldatetime.
Return Types
int
Remarks
This function is equivalent to DATEPART(yy, date).
Examples
This example returns the number of the year from the date 03/12/1998.
YEAR( '03/12/1998' ) -- result is 1998
YEAR( [Birth Date] ) -- result is 1964 for contact 'Chris Huffman'

Close Window

Operators

+ (Add )
Adds two numbers. This addition arithmetic operator can also add a number, in days, to a date.
Syntax
expression + expression
Arguments
expression
Is any valid expression of any of the data types in the numeric category.
Examples
A. Use the addition operator
[Field 1] + [Field 2]
[Field 1] + 100
B. Use the addition operator to add days to date and time values
[Birth Date] + 7 -- add 7 days to birth date

+ (String Concatenation )
An operator in a string expression that concatenates two or more character or binary strings, fields, or a combination of strings and field names into one expression (a string operator).
Syntax
expression + expression
Arguments
expression
Is any valid expression of any of the data types in the character and binary data type category, except the image, ntext, or text data types. Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression.
An explicit conversion (CONVERT or CAST) to character data must be used when concatenating binary strings and any characters between the binary strings.
Examples
A. Use string concatenation
'Addr1 = ' + [Address 1] + ', Addr2 = ' + [Address 2]

B. Combine string and date data types
'Birth date = ' + CAST( [Birth Date] as varchar(64) )

- (Subtract )
Subtracts two numbers. This subtraction arithmetic operator can also subtract a number, in days, from a date.
Syntax
expression - expression
Arguments
expression
Is any valid expression of any of the data types of the numeric data type category.
Examples
A. Use the substraction operator
[Field 1] - [Field 2]
[Field 1] - 100
Parentheses can be used to change the order of execution. Calculations inside parentheses are evaluated first. If parentheses are nested, the most deeply nested calculation has precedence.
B. Use date subtraction
This example subtracts a number of days from a datetime date.
[Birth Date] - 7 -- subtract 7 days from birth date

-- (Comment )
Indicates user-provided text. Comments can be inserted on a separate line, nested (-- only) at the end of a line.
Syntax
-- text_of_comment
Arguments
text_of_comment
Is the character string containing the text of the comment.
Remarks
Use -- for single-line or nested comments. Comments inserted with -- are delimited by the newline character.
There is no maximum length for comments.
Examples
[Field 1] + [Field 2] -- this is comment

* (Multiply )
Multiplies two expressions (an arithmetic multiplication operator).
Syntax
expression * expression
Arguments
expression
Is any valid expression of any of the data types of the numeric data type category except the datetime or smalldatetime data types.
Examples
[Field 1] * [Field 2]
[Field 1] * 100

/ (Divide )
Divides one number by another (an arithmetic division operator).
Syntax
dividend / divisor
Arguments
dividend
Is the numeric expression to divide. dividend can be any valid expression