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 ( float_expression )
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
AGE
Returns the age of a date in years.
Syntax
AGE ( datetime_expression )
Arguments
datetime_expression
Is an expression of the datetime data type.
Return Types
int
Examples
AGE( [Birth Date] )
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.
| Control character |
Value |
| Tab |
CHAR(9) |
| Line feed |
CHAR(10) |
| Carriage return |
CHAR(13) |
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).
| Without century (yy) |
With century (yyyy) |
Standard |
Input/Output** |
| - |
0 or 100 (*) |
Default |
mon dd yyyy hh:miAM (or PM) |
| 1 |
101 |
USA |
mm/dd/yy |
| 2 |
102 |
ANSI |
yy.mm.dd |
| 3 |
103 |
British/French |
dd/mm/yy |
| 4 |
104 |
German |
dd.mm.yy |
| 5 |
105 |
Italian |
dd-mm-yy |
| 6 |
106 |
- |
dd mon yy |
| 7 |
107 |
- |
Mon dd, yy |
| 8 |
108 |
- |
hh:mm:ss |
| - |
9 or 109 (*) |
Default + milliseconds |
mon dd yyyy hh:mi:ss:mmmAM (or PM) |
| 10 |
110 |
USA |
mm-dd-yy |
| 11 |
111 |
JAPAN |
yy/mm/dd |
| 12 |
112 |
ISO |
yymmdd |
| - |
13 or 113 (*) |
Europe default + milliseconds |
dd mon yyyy hh:mm:ss:mmm(24h) |
| 14 |
114 |
- |
hh:mi:ss:mmm(24h) |
| - |
20 or 120 (*) |
ODBC canonical |
yyyy-mm-dd hh:mi:ss(24h) |
| - |
21 or 121 (*) |
ODBC canonical (with milliseconds) |
yyyy-mm-dd hh:mi:ss.mmm(24h) |
| - |
126(***) |
ISO8601 |
yyyy-mm-dd Thh:mm:ss:mmm(no spaces) |
| - |
130* |
Kuwaiti |
dd mon yyyy hh:mi:ss:mmmAM |
| - |
131* |
Kuwaiti |
dd/mm/yy hh:mi:ss:mmmAM |
* 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.
| Value |
Output |
| 0 (default) |
Six digits maximum. Use in scientific notation, when appropriate. |
| 1 |
Always eight digits. Always use in scientific notation. |
| 2 |
Always 16 digits. Always use in scientific notation. |
In the following table, the column on the left represents the style
value for money or smallmoney conversion to character data.
| Value |
Output |
| 0 (default) |
No commas every three digits to the left of the decimal point,
and two digits to the right of the decimal point; for example, 4235.98. |
| 1 |
Commas every three digits to the left of the decimal point,
and two digits to the right of the decimal point; for example, 3,510.92. |
| 2 |
No commas every three digits to the left of the decimal
point, and four digits to the right of the decimal point; for example,
4235.9819. |
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.
| Datepart |
Abbreviations |
| Year |
yy, yyyy |
| quarter |
qq, q |
| Month |
mm, m |
| dayofyear |
dy, y |
| Day |
dd, d |
| Week |
wk, ww |
| Hour |
hh |
| minute |
mi, n |
| second |
ss, s |
| millisecond |
ms |
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.
| Datepart |
Abbreviations |
| Year |
yy, yyyy |
| quarter |
qq, q |
| Month |
mm, m |
| dayofyear |
dy, y |
| Day |
dd, d |
| Week |
wk, ww |
| Hour |
hh |
| minute |
mi, n |
| second |
ss, s |
| millisecond |
ms |
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.
| Datepart |
Abbreviations |
| year |
yy, yyyy |
| quarter |
qq, q |
| month |
mm, m |
| dayofyear |
dy, y |
| day |
dd, d |
| week |
wk, ww |
| weekday |
dw |
| hour |
hh |
| minute |
mi, n |
| second |
ss, s |
| millisecond |
ms |
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.
| Datepart |
Abbreviations |
| year |
yy, yyyy |
| quarter |
qq, q |
| month |
mm, m |
| dayofyear |
dy, y |
| day |
dd, d |
| week |
wk, ww |
| weekday |
dw |
| hour |
hh |
| minute |
mi, n |
| second |
ss, s |
| millisecond |
ms |
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, xxxx') = 1, where xxxx 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
FORMAT
The syntax of the format function is the following:
o Format (expression;format)
Ex: if field [Field1]=2343.3 then Format([Field1], "###0.00")
returns 2343.30
The format argument is a string of characters with
one or more of the following characters:
| @ |
A character should appear at this position. If there is no character
for this position, a space is inserted. If there are more than one @,
they are applied from right to left. |
| & |
Same as @, except that no space is inserted if there is no character
at this position. |
| ! |
Reverses the order in which @ and & are applied (ie. becomes
from left to right). |
| < |
Converts all characters to lowercase. |
| > |
Converts all characters to uppercase. |
Ex:
Format(4142345678, "(@@@) @@@-@@@@")=(414) 234-5678
Format(2345678, "(@@@) @@@-@@@@")=( ) 234-5678
Format(2345678, "!(@@@) @@@-@@@@")=(234) 567-8
Number formatting:
| · (blank)The digit is returned without formatting
|
| 0 |
A digit is supposed to appear at this position. If there
is none, then 0 is displayed. If the format string contains more 0 than
the number to be formatted, extra 0s are added in front or at the end. |
| # |
Similar to 0 except that nothing is inserted if no digit
is to appear |
| |
Combined with 0 or #, specifies the number of digits which
should appear on each side of the decimal character. |
| % |
Multiplies the number by 100 and adds a % sign. |
| , |
Inserted in series of 0 or #, indicates the thousand separator.
A double comma indicates that the number should be divided by 1000. |
| E-, E+ |
If the format string contains at least a 0 or #, converts the number
to scientific notation. |
Ex: Format(2343.3,"0000.00") =
02343. 3 0
Format(2343.3,"$###,###.00") = $2,343.00 Format(45, "+###")
= +45
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
ISNULL
Replaces NULL
with the specified replacement value.
Syntax
ISNULL ( check_expression , replacement_value
)
Arguments
check_expression
Is the expression
to be checked for NULL. check_expression can be of any type.
replacement_value
Is the expression
to be returned if check_expression is NULL. replacement_value
must have the same type as check_expresssion.
Return
Types
Returns the same type as check_expression.
Remarks
The value of check_expression is returned if it is not NULL; otherwise,
replacement_value is returned.
Examples:
ISNULL(
[Field 1],0 ) + ISNULL(
[Field 2],0 )
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 4 for contact 'Chris Huffman'
MONTH('03/12/1998') -- result is 3
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 xxx.
REPLACE( 'abcdefghicde', 'cde', 'xxx' ) -- result is abxxxfghixxx
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.
| Example |
Result |
| ROUND(748.58, -4) |
0 |
ROUND returns a rounded numeric_expression, regardless of
data type, when length is a negative number.
| Examples |
Result |
| ROUND(748.58, -1) |
750.00 |
| ROUND(748.58, -2) |
700.00 |
| ROUND(748.58, -3) |
1000.00 |
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
