Scalar Functions
The following scalar functions are supported in expressions, in calc scripts and formulas alike. They work exactly like the SQL functions with the same name (and in fact call the corresponding SQL function).
Mathematical Functions
| Function | Function | Function | Function |
|---|---|---|---|
abs | acos | asin | atan |
atn2 | ceiling | cos | cot |
degrees | exp | floor | log |
log10 | pi | power | radians |
rand | round | sign | sin |
sqrt | square | tan |
String Functions
Conditional Functions
| Function | Function | Function | Function |
|---|---|---|---|
choose | coalesce | iif | isnull |
isnumeric | nullif | greatest | least |
Bitwise Functions
| Function | Function | Function | Function |
|---|---|---|---|
bit_count | get_bit | left_shift | right_shift |
set_bit |
Date Functions
| Function | Function | Function |
|---|---|---|
datefromparts | day | month |
year |
Conversion Functions
| Function | Function |
|---|---|
convert | try_convert |
Other Functions
| Function |
|---|
newid |
Function Reference
Mathematical Functions
abs
Returns the absolute (positive) value of a number.
abs(-15) → 15
abs(15.5) → 15.5
acos
Returns the arc cosine (inverse cosine) of a number in radians.
acos(0.5) → 1.0471975511966 (approximately π/3)
acos(1) → 0
asin
Returns the arc sine (inverse sine) of a number in radians.
asin(0.5) → 0.523598775598299 (approximately π/6)
asin(1) → 1.5707963267949 (approximately π/2)
atan
Returns the arc tangent (inverse tangent) of a number in radians.
atan(1) → 0.785398163397448 (approximately π/4)
atan(0) → 0
atn2
Returns the arc tangent of the quotient of two numbers (y/x) in radians.
atn2(1, 1) → 0.785398163397448 (45 degrees in radians)
atn2(1, 0) → 1.5707963267949 (90 degrees in radians)
ceiling
Returns the smallest integer greater than or equal to the specified number.
ceiling(12.34) → 13
ceiling(-12.34) → -12
cos
Returns the cosine of an angle specified in radians.
cos(0) → 1
cos(pi()) → -1
cot
Returns the cotangent of an angle specified in radians.
cot(0.785398163397448) → 1 (approximately, for π/4)
degrees
Converts radians to degrees.
degrees(pi()) → 180
degrees(1) → 57.2957795130823
exp
Returns e raised to the specified power.
exp(1) → 2.71828182845905 (e)
exp(2) → 7.38905609893065
floor
Returns the largest integer less than or equal to the specified number.
floor(12.34) → 12
floor(-12.34) → -13
log
Returns the natural logarithm of a number.
log(2.71828182845905) → 1 (approximately)
log(10) → 2.30258509299405
log10
Returns the base-10 logarithm of a number.
log10(100) → 2
log10(1000) → 3
pi
Returns the constant value of π (pi).
pi() → 3.14159265358979
power
Returns the value of a number raised to a specified power.
power(2, 3) → 8
power(5, 2) → 25
radians
Converts degrees to radians.
radians(180) → 3.14159265358979 (π)
radians(90) → 1.5707963267949 (π/2)
rand
Returns a random float value between 0 and 1.
rand() → 0.713591993212924 (varies each time)
rand(5) → 0.713591993212924 (same seed returns same value)
round
Rounds a number to a specified number of decimal places.
round(123.456, 2) → 123.460
round(123.456, 0) → 123.000
round(123.456, -1) → 120.000
sign
Returns the sign of a number: -1 for negative, 0 for zero, 1 for positive.
sign(25) → 1
sign(-25) → -1
sign(0) → 0
sin
Returns the sine of an angle specified in radians.
sin(0) → 0
sin(pi()/2) → 1
sqrt
Returns the square root of a number.
sqrt(16) → 4
sqrt(2) → 1.41421356237310
square
Returns the square of a number.
square(5) → 25
square(3.5) → 12.25
tan
Returns the tangent of an angle specified in radians.
tan(0) → 0
tan(pi()/4) → 1
String Functions
ascii
Returns the ASCII code value of the leftmost character of a string.
ascii('A') → 65
ascii('ABC') → 65
char
Converts an ASCII code to a character.
char(65) → 'A'
char(97) → 'a'
charindex
Returns the starting position of a substring within a string.
charindex('world', 'hello world') → 7
charindex('x', 'hello world') → 0 (not found)
concat
Concatenates two or more strings together.
concat('Hello', ' ', 'World') → 'Hello World'
concat('Value: ', 100) → 'Value: 100'
concat_ws
Concatenates strings with a separator.
concat_ws(', ', 'Apple', 'Orange', 'Banana') → 'Apple, Orange, Banana'
concat_ws('-', '2024', '03', '15') → '2024-03-15'
difference
Returns the SOUNDEX difference between two strings (0-4, where 4 is most similar).
difference('Smith', 'Smythe') → 4
difference('Smith', 'Johnson') → 1
format
Formats a value with a specified format.
format(12345.67, 'N2') → '12,345.67'
format(getdate(), 'yyyy-MM-dd') → '2026-03-12'
left
Returns the leftmost characters from a string.
left('Hello World', 5) → 'Hello'
left('ABCDEF', 3) → 'ABC'
len
Returns the number of characters in a string (excluding trailing spaces).
len('Hello World') → 11
len('Test ') → 4
lower
Converts a string to lowercase.
lower('HELLO WORLD') → 'hello world'
lower('MixedCase') → 'mixedcase'
ltrim
Removes leading spaces from a string.
ltrim(' Hello') → 'Hello'
ltrim(' Test ') → 'Test '
nchar
Returns the Unicode character for the specified integer code.
nchar(65) → 'A'
nchar(8364) → '€'
patindex
Returns the starting position of a pattern in a string.
patindex('%world%', 'hello world') → 7
patindex('%[0-9]%', 'abc123') → 4
quotename
Returns a string enclosed in delimiters.
quotename('TableName') → '[TableName]'
quotename('O''Brien', '''') → '''O''''Brien'''
replace
Replaces all occurrences of a substring with another substring.
replace('Hello World', 'World', 'SQL') → 'Hello SQL'
replace('aaa', 'a', 'b') → 'bbb'
replicate
Repeats a string a specified number of times.
replicate('*', 5) → '*****'
replicate('ab', 3) → 'ababab'
reverse
Reverses a string.
reverse('Hello') → 'olleH'
reverse('12345') → '54321'
right
Returns the rightmost characters from a string.
right('Hello World', 5) → 'World'
right('ABCDEF', 3) → 'DEF'
rtrim
Removes trailing spaces from a string.
rtrim('Hello ') → 'Hello'
rtrim(' Test ') → ' Test'
soundex
Returns a four-character SOUNDEX code for phonetic comparison.
soundex('Smith') → 'S530'
soundex('Smythe') → 'S530'
space
Returns a string of repeated spaces.
space(5) → ' '
concat('Hello', space(3), 'World') → 'Hello World'
str
Converts a number to a string with specified length and decimal places.
str(123.456, 6, 2) → '123.46'
str(12, 5) → ' 12'
string_escape
Escapes special characters in a string.
string_escape('Hello\nWorld', 'json') → 'Hello\\nWorld'
string_escape('C:\Temp', 'json') → 'C:\\Temp'
stuff
Deletes characters from a string and inserts new characters.
stuff('ABCDEF', 2, 3, '123') → 'A123EF'
stuff('Hello World', 7, 5, 'SQL') → 'Hello SQL'
substring
Extracts a portion of a string.
substring('Hello World', 7, 5) → 'World'
substring('ABCDEF', 2, 3) → 'BCD'
translate
Replaces characters in a string based on a mapping.
translate('2*[3+4]/{7-2}', '[]{}', '()()') → '2*(3+4)/(7-2)'
translate('ABC123', '123', 'XYZ') → 'ABCXYZ'
trim
Removes leading and trailing spaces (or specified characters) from a string.
trim(' Hello ') → 'Hello'
trim('.' from '...test...') → 'test'
unicode
Returns the Unicode integer value of the first character.
unicode('A') → 65
unicode('€') → 8364
unistr
Returns the Unicode string for the specified escape sequences.
unistr('Hello\0020World') → 'Hello World'
unistr('\00A9') → '©'
upper
Converts a string to uppercase.
upper('hello world') → 'HELLO WORLD'
upper('mixedCase') → 'MIXEDCASE'
Conditional Functions
choose
Returns the item at the specified index from a list of values.
choose(2, 'Apple', 'Orange', 'Banana') → 'Orange'
choose(1, 'First', 'Second', 'Third') → 'First'
coalesce
Returns the first non-null value from a list of arguments.
coalesce(null, null, 'Hello', 'World') → 'Hello'
coalesce(null, 100, 200) → 100
iif
Returns one value if a condition is true, another if false.
iif(10 > 5, 'Yes', 'No') → 'Yes'
iif(3 < 2, 'True', 'False') → 'False'
isnull
Replaces NULL with a specified replacement value.
isnull(null, 'Default') → 'Default'
isnull(100, 0) → 100
isnumeric
Returns 1 if the expression is a valid numeric value, otherwise 0.
isnumeric('123') → 1
isnumeric('ABC') → 0
isnumeric('12.34') → 1
nullif
Returns NULL if two expressions are equal, otherwise returns the first expression.
nullif(5, 5) → NULL
nullif(5, 10) → 5
nullif('Hello', 'Hello') → NULL
greatest
Returns the largest value from a list of arguments.
greatest(10, 20, 5, 15) → 20
greatest('Apple', 'Banana', 'Cherry') → 'Cherry'
least
Returns the smallest value from a list of arguments.
least(10, 20, 5, 15) → 5
least('Apple', 'Banana', 'Cherry') → 'Apple'
Bitwise Functions
bit_count
Returns the number of bits set to 1 in an integer.
bit_count(7) → 3 (binary: 111)
bit_count(15) → 4 (binary: 1111)
get_bit
Returns the value of a specific bit (0 or 1) at a given position.
get_bit(5, 0) → 1 (binary 101, rightmost bit)
get_bit(5, 2) → 1 (binary 101, leftmost bit)
left_shift
Shifts bits to the left by a specified number of positions.
left_shift(5, 1) → 10 (binary: 101 → 1010)
left_shift(3, 2) → 12 (binary: 11 → 1100)
right_shift
Shifts bits to the right by a specified number of positions.
right_shift(8, 1) → 4 (binary: 1000 → 100)
right_shift(12, 2) → 3 (binary: 1100 → 11)
set_bit
Sets a specific bit to 1 or 0 at a given position.
set_bit(0, 2, 1) → 4 (sets bit at position 2 to 1)
set_bit(7, 1, 0) → 5 (sets bit at position 1 to 0)
Date Functions
datefromparts
Constructs a date from year, month, and day values.
datefromparts(2026, 3, 12) → '2026-03-12'
datefromparts(2024, 12, 31) → '2024-12-31'
day
Returns the day of the month (1-31) from a date.
day('2026-03-12') → 12
day('2024-01-31') → 31
month
Returns the month (1-12) from a date.
month('2026-03-12') → 3
month('2024-12-25') → 12
year
Returns the year from a date.
year('2026-03-12') → 2026
year('1999-12-31') → 1999
Conversion Functions
The first argument is the SQL type to convert to: int, bigint, smallint, tinyint, bit, float, real, money, date, datetime, datetime2, decimal(p[,s]), numeric(p[,s]), nvarchar(n|max) or varchar(n|max). An optional third argument is the SQL conversion style.
convert
Converts a value to the given type. Fails when the value cannot be converted.
convert(int, 5.7) → 5
convert(decimal(18,2), '1.25') → 1.25
try_convert
Like convert, but returns NULL (an empty value) when the value cannot be converted.
try_convert(int, '12') → 12
try_convert(int, 'abc') → NULL
Other Functions
newid
Generates a new unique identifier (GUID/UUID).
newid() → '6F9619FF-8B86-D011-B42D-00C04FC964FF' (unique each time)