Skip to main content
Previous sectionNext section

TSQL Functions

Supported Functions

The following TSQL functions are implemented for InterSystems IRIS® data platform.

ABS

ABS(num)

Returns the absolute value of num. Thus both 123.99 and –123.99 return 123.99.

ACOS

ACOS(float)

Arc cosine: returns the angle in radians whose cosine is float. Thus 1 returns 0.

ASCII

ASCII(char)

Returns the integer value corresponding to the first character in string char. Thus, ASCII('A') returns 65.

ASCII is functionally identical to UNICODE. The reverse of this function is CHAR.

ASIN

ASIN(float)

Arc sine: returns the angle in radians whose sine is float. Thus 1 returns 1.570796326...

ATAN

ATAN(float)

Arc tangent: returns the angle in radians whose tangent is float. Thus 1 returns .785398163...

AVG

AVG(numfield) 
AVG(DISTINCT numfield)

Aggregate function: used in a query to return the average of the values in the numfield column. For example, SELECT AVG(Age) FROM Sample.Person. AVG(DISTINCT numfield) averages the number of unique values in the numfield column. Fields with NULL are ignored.

CAST

CAST(expression AS datatype)

Returns the expression converted to the specified datatype. CAST can be used with any supported data type. For further details, refer to Data Types in the InterSystems SQL Reference. CAST supports user-defined data types created using the sp_addtype stored procedure.

When expression is a date value string, such as '2004–11–23' and datatype is TIMESTAMP or DATETIME, a time value of '00:00:00' is supplied.

When expression is a time value string, such as '1:35PM' and datatype is TIMESTAMP or DATETIME, the time is converted to a 24-hour clock, the AM or PM suffix is removed, a missing seconds interval is filled in with zeros, and the default date value of '1900–01–01' is supplied. Thus '1:35PM' is converted to '1900–01–01 13:35:00'.

When expression is a date value string, such as '2004–11–23' and datatype is DATE, the date is returned in InterSystems IRIS $HOROLOG date format, such as 60703 (March 14, 2007).

InterSystems TSQL does not support data type XML. However, instead of generating an error during compilation, CAST(x AS XML) in SQL mode generates CAST(x AS VARCHAR(32767)). In procedure mode, CAST(x AS XML) does not generate any conversion.

See CONVERT.

CEILING

CEILING(num)

Returns the closest integer greater than or equal to num. Thus 123.99 returns 124, –123.99 returns –123.

The Sybase CEIL synonym is not supported.

CHAR

CHAR(num)

Returns the character corresponding to the integer value num. Thus CHAR(65) returns A.

CHAR is functionally identical to NCHAR. The reverse of this function is ASCII.

CHAR_LENGTH / CHARACTER_LENGTH

CHAR_LENGTH(string) 
CHARACTER_LENGTH(string)

Returns the number of characters in string.

CHARINDEX

CHARINDEX(seekstring,target[,startpoint])

Returns the position in target (counting from 1) corresponding to first character of the first occurrence of seekstring. You can use the optional startpoint integer to specify where to begin the search. The return value counts from the beginning of target, regardless of the startpoint. If startpoint is not specified, specified as 0, 1, or as a negative number, target is searched from the beginning. CHARINDEX returns 0 if seekstring is not found.

COALESCE

COALESCE(expression1,expression2,...)

Returns the first non-null expression from the specified list of expressions.

COL_NAME

COL_NAME(object_id,column_id)

Returns the name of the column. Can be used in procedure code or trigger code.

TSQL supports the two-argument form of this function. It does not support a third argument.

The following example returns the column name of the 4th column of Sample.Person:

  SET sql=2
  SET sql(1)="SELECT 'column name'=COL_NAME(id,4) FROM Sample.Person"
  SET sql(2)="WHERE id=OBJECT_ID('Sample.Person')"
  SET statement=##class(%SQL.Statement).%New()
  SET statement.%Dialect="MSSQL"
  SET status=statement.%Prepare(.sql)
  SET result=statement.%Execute()
  DO result.%Display()
Copy code to clipboard

COL_NAME does not support the Sybase third argument.

CONVERT

CONVERT(datatype,expression [,style])

Returns the expression converted to the specified datatype.

  • BIT: When datatype is BIT and expression is a boolean value: if the input value is a non-zero number, the result is 1. if the input value is 0, the result is 0. If the input value is the string 'TRUE' (case insensitive), the result is 1. If the input value is the string 'FALSE' (case insensitive), the result is 0. If the input value is NULL, the result is NULL. Any other input value generates an SQLCODE -141 error.

  • CHAR: When the length of CHAR is not specified, converts to a length of 30 characters.

  • DATETIME: When datatype is datetime or timestamp:

    • When expression is a date value string, such as '2004–11–23', a time value of '00:00:00' is supplied.

    • When expression is a time value string, such as '1:35PM' and datatype is datetime or timestamp, the time is converted to a 24-hour clock, the AM or PM suffix is removed, a missing seconds interval is filled in with zeros, and the default date value of '1900–01–01' is supplied. Thus '1:35PM' is converted to '1900–01–01 13:35:00'.

    • When expression is the empty string or a string of one or more blank spaces, CONVERT returns '1900–01–01 00:00:00'.

    By default DATETIME data type is mapped to %Library.TimeStamp. CONVERT also supports data type mapping of DATETIME to %Library.PosixTime.

    CONVERT supports the DATETIME2 data type. InterSystems IRIS maps DATETIME2 to system-defined DDL mapping %Library.TimeStamp. This mapping is supplied with new installs; if you are using an upgrade install, you may need to create this mapping.

    CONVERT supports user-defined data types created using the sp_addtype stored procedure.

The optional style argument is used to specify a date/time format when converting a datetime or timestamp value to a string. By specifying various style codes you can return a dates and times in a variety of different formats. The available style codes are 100 through 116, 120 through 123, 126, 130 and 131, 136 through 140. (the corresponding codes 0 through 7 and 10 through 40 return the same values with two-digit years); The default style for a datetime is 0:

mon dd yyyy hh:mmAM

The following are some of the supported datetime styles for Sybase:

15 / 115 = format dd/[yy]yy/mm
16 / 116 = format mon dd yyyy HH:mm:ss 
22 / 122 = format [yy]yy/mm/dd HH:mm AM (or PM) 
23 / 123 = format [yy]yy-mm-ddTHH:mm:ss 
36 / 136 = format hh:mm:ss.zzzzzzAM(PM) 
37 / 137 = format hh:mm.ss.zzzzzz 
38 / 138 = format mon dd [yy]yy hh:mm:ss.zzzzzzAM(PM) 
39 / 139 = format mon dd [yy]yy HH:mm:ss.zzzzzz 
40 / 140 = format yyyy-mm-dd hh:mm:ss.zzzzzz 

The 20 & 21 (120 & 121) style codes return the ODBC timestamp format; 20 truncates to whole seconds, 21 returns fractional seconds:

yyyy-mm-dd hh:mm:ss.fff

For further details, refer to the functionally identical InterSystems SQL CONVERT function in the InterSystems SQL Reference.

See CAST.

COS

COS(float)

Cosine: returns the cosine of the angle specified in float. Thus 1 returns .540302305...

COT

COT(float)

Cotangent: returns the cotangent of the angle specified in float. Thus 1 returns .64209261593...

COUNT

COUNT(field) 
COUNT(DISTINCT field) 
COUNT(*) 
COUNT(1)

Aggregate function: used in a query to return the count of the values in the field column. Fields with NULL are not counted. For example, SELECT COUNT(Name) FROM Sample.Person. COUNT(*) and COUNT(1) are synonyms, they count all rows. COUNT(DISTINCT field) counts the number of unique values in the field column. Fields with NULL are not counted.

CURRENT_DATE

CURRENT_DATE 
CURRENT DATE

Returns the current local date in the following format:

yyyy-mm-dd

The two syntax forms, with and without an underscore, are identical. Note that no parentheses are used with this function.

This function is provided for compatibility with SQL Anywhere; it is supported by both the Sybase and MSSQL dialects.

CURRENT_TIME

CURRENT_TIME 
CURRENT TIME

Returns the current local time in the following format:

hh:mm:ss

Time is specified using a 24-hour clock, Fractional seconds are not returned.

The two syntax forms, with and without an underscore, are identical. Note that no parentheses are used with this function.

This function is provided for compatibility with SQL Anywhere; it is supported by both the Sybase and MSSQL dialects.

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP 
CURRENT TIMESTAMP

Returns the current local date and time in the following format:

yyyy-mm-dd hh:mm:ss

Time is specified using a 24-hour clock, Fractional seconds are not returned.

The two syntax forms, with and without an underscore, are identical. Note that no parentheses are used with this function.

CURRENT_USER

CURRENT_USER

Returns the name of the current user.

Note that no parentheses are used with this function.

DATALENGTH

DATALENGTH(expression)

Returns an integer specifying the number of bytes used to represent expression. Thus 'fred' returns 4, and +007.500 returns 3.

DATEADD

DATEADD(code,num,date)

Returns the value of date modified by adding the interval specified in code the num number of times. The date can be a date, time, or date/time string in a variety of formats. You can specify any of the following code values, either the abbreviation (left column) or the name (right column):

yy Year
qq Quarter
mm Month
dy DayofYear
dd Day
dw, w Weekday
wk Week
hh Hour
mi Minute
ss Second
ms Millisecond

Code values are not case-sensitive. Day, DayofYear, and Weekday all return the same value.

The value returned by DATEADD always includes both date and time in the format:

yyyy-mm-dd hh:mm:ss.n

Fractional seconds are only returned if the source contained fractional seconds.

If a date is not specified (that is, if date contains only a time value), it defaults to 1/1/1900.

If a time is not specified in date, it defaults to 00:00:00. Hours are always returned based on a 24-hour clock.

DATEDIFF

DATEDIFF(code,startdate,enddate)

Returns the number of code intervals between startdate and enddate. The two dates can be a date, a time, or a date/time string. in the following format:

yyyy-mm-dd hh:mm:ss.n

You can specify any of the following code values, either the abbreviation (left column) or the name (right column):

yy Year
mm Month
dd Day
dw, w Weekday
wk Week
hh Hour
mi Minute
ss Second
ms Millisecond

Code values are not case-sensitive. Day, DayofYear, and Weekday all return the same value.

If a date is not specified (that is, if startdate or enddate contains only a time value), it defaults to 1/1/1900.

If a time is not specified in startdate or enddate, it defaults to 00:00:00.

DATENAME

DATENAME(code,date)

Returns the value of the part of the date specified by code as a string. The date can be a date, time, or date/time string in a variety of formats. date must be specified as a quoted string; code permits, but does not require enclosing quotes. Available code values are:

yyyy, yy
year
Year. Returns a four-digit year. If a two-digit year is specified, DATENAME supplies '19' as first two digits.
qq, q
quarter
Quarter. Returns an integer 1 through 4.
mm, m
month
Month. Returns the full name of the month. For example, 'December'.
dy, y
dayofyear
Day of Year. Returns an integer count of days 1 through 366.
dd, d
day
Day of Month. Returns an integer count 1 through 31.
wk, ww
week
Week of Year. Returns an integer count 1 through 53.
dw, w
weekday
Day of Week. Returns the number of the day of the week, counting from Sunday. For example, 3 is Tuesday.
hh
hour
Hour. Returns the hour of the day (24–hour clock), an integer 0 through 23.
mi, n
minute
Minute. Returns an integer 0 through 59.
ss, s
second
Second. Returns a decimal number 0 through 59 which may have a fractional part representing milliseconds.
ms
millisecond
Millisecond. Returns the fractional part of a second as an integer.

Code values are not case-sensitive.

If a date is not specified, it defaults to 1/1/1900. Two-digit years default to 19xx.

If a time is not specified, it defaults to 00:00:00. Hours are always returned based on a 24-hour clock. Seconds are always returned with fractional seconds, if fractional seconds are defined. Milliseconds are returned as an integer, not a decimal fraction.

DATEPART

DATEPART(code,date)

Returns the value of the part of the date specified in code as an integer. The date can be a date, time, or date/time string in a variety of formats. Available code values are listed in DATENAME.

DAY

DAY(date)

Returns the day portion of the specified date or date/time string. The date can be specified in ODBC timestamp format:

yyyy-mm-dd hh:mm:ss.n

The date must contain a date component. The date separator must be a hyphen (-).

The date can also be specified in InterSystems IRIS $HOROLOG date format, such as 60703 (March 14, 2007).

DB_NAME

DB_NAME(database-id)

Returns the current namespace name. The database-id argument is optional.

DEGREES

DEGREES(float)

Converts an angle measurement in radians to the corresponding measurement in degrees.

ERROR_MESSAGE

When invoked from within a CATCH block, returns the current error message. Otherwise, returns NULL.

ERROR_NUMBER

When invoked from within a CATCH block, returns the current SQLCODE error. Otherwise, returns NULL.

EXEC

EXEC(@var)

Executes dynamic SQL at runtime, as shown in the following example:

DECLARE @dyncode VARCHAR(200)
SELECT @dyncode='SELECT TOP 4 Name,Age FROM Sample.Person'
EXEC(@dyncode)
Copy code to clipboard

Compare this dynamic execution with the EXECUTE command that executes a stored procedure.

EXP

EXP(num)

Returns the exponential of num. This is the e constant (2.71828182) raised to the power of num. Thus EXP(2) returns 7.3890560989.

FLOOR

FLOOR(num)

Returns the closest integer less than or equal to num. Thus 123.99 returns 123, –123.99 returns –124.

GETDATE

GETDATE()

Returns the current local date and time in the following format:

yyyy-mm-dd hh:mm:ss.n

Time is specified using a 24-hour clock, Fractional seconds are returned.

GETUTCDATE

GETUTCDATE()

Returns the current UTC (Greenwich Mean Time) date and time in the following format:

yyyy-mm-dd hh:mm:ss.n

Time is specified using a 24-hour clock, Fractional seconds are returned.

HOST_NAME

HOST_NAME()

Returns the system name of the current host system.

INDEX_COL

INDEX_COL(table_name,index_id,key,[,user_id])

Returns the name of the indexed column in the specified table. table_name can be fully qualified. index_id is the number of the table's index. key is a key in the index, a value between 1 and sysindexes.keycnt (for a clustered index) or sysindexes.keycnt+1 (for a non-clustered index). user_id is parsed but ignored.

ISNULL

ISNULL(expr,default)

If expr is NULL, returns default. If expr is not NULL, returns expr.

ISNUMERIC

ISNUMERIC(expression)

A boolean function that returns 1 if expression is a valid numeric value; otherwise, returns 0.

If the specified expression is a field with a null value, ISNUMERIC returns null.

LEFT

LEFT(string,int)

Returns int number of characters from string, counting from the left. If int is larger than string, the full string is returned. See RIGHT.

LEN

LEN(string)

Returns the number of characters in string.

LOG

LOG(num)

Returns the natural logarithm of num. Thus LOG(2) returns .69314718055.

LOG10

LOG10(num)

Returns the base-10 logarithm of num. Thus LOG10(2) returns .301029995663.

LOWER

LOWER(string)

Returns string with all uppercase letters converted to lowercase. See UPPER.

LTRIM

LTRIM(string)

Removes leading blanks from string.

If string consists entirely of blank spaces, the dialect determines behavior:

  • Sybase: returns NULL.

  • MSSQL: returns the empty string.

See RTRIM.

MAX

MAX(numfield)

Aggregate function: used in a query to return the largest (maximum) of the values in the numfield column. For example:

SELECT MAX(Age) FROM Sample.Person
Copy code to clipboard

Fields with NULL are ignored.

MIN

MIN(numfield)

Aggregate function: used in a query to return the smallest (minimum) of the values in the numfield column. For example:

SELECT MIN(Age) FROM Sample.Person
Copy code to clipboard

Fields with NULL are ignored.

MONTH

MONTH(date)

Returns the month portion of the specified date or date/time string. The date can be specified in ODBC timestamp format:

yyyy-mm-dd hh:mm:ss.n

The date separator must be a hyphen (-). Dates in any other format return 0.

The date can also be specified in InterSystems IRIS $HOROLOG date format, such as 60703 (March 14, 2007).

NCHAR

NCHAR(num)

Returns the character corresponding to the integer value num. Thus NCHAR(65) returns A.

NCHAR is functionally identical to CHAR. The reverse of this function is ASCII.

NEWID

NEWID()

Returns a unique value of a type compatible with the SQL Server UNIQUEIDENTIFIER data type. UNIQUEIDENTIFIER is a system-generated 16-byte binary string, also known as a a globally unique ID (GUID). A GUID is used to synchronize databases on occasionally connected systems. A GUID is a 36-character string consisting of 32 hexadecimal numbers separated into five groups by hyphens. InterSystems TSQL does not support UNIQUEIDENTIFIER; it instead uses VARCHAR(36) as the data type for a Globally Unique ID.

The NEWID function takes no arguments. Note that the argument parentheses are required.

NEWID() can be used to specify the DEFAULT value when defining a field.

The corresponding InterSystems SQL function is $TSQL_NEWID:

SELECT $TSQL_NEWID()
Copy code to clipboard

NOW

NOW(*)

Returns the current local date and time in the following format:

yyyy-mm-dd hh:mm:ss

Time is specified using a 24-hour clock, Fractional seconds are not returned.

Note that the asterisk within the parentheses is required.

NULLIF

NULLIF(expr1,expr2)

Returns NULL if expr1 is equivalent to expr2. Otherwise, returns expr1.

OBJECT_ID

OBJECT_ID(objname,objtype)

Takes the object name as a quoted string, and optionally the object type, and returns the corresponding object ID of the specified object as an integer. The available objtype values are as follows: RI = FOREIGN KEY constraint; K = PRIMARY KEY or UNIQUE constraint; P = Stored procedure; S = System table; TR = Trigger; U = User table; V = View. If objtype is omitted, OBJECT_ID tests all object types and returns the first match.

CREATE PROCEDURE GetObjIds
AS SELECT OBJECT_ID('Sample.Person','U'),OBJECT_ID('Sample.Person_Extent','P')
GO
Copy code to clipboard

Returns NULL if objname does not exist, or if the optional objtype is specified and does not match the objname. Can be used within procedure code or trigger code. The inverse of OBJECT_NAME.

OBJECT_NAME

OBJECT_NAME(id)

Takes the object ID integer and returns the corresponding object name of the specified object. Returns the empty string if id does not exist. Can be used within procedure code or trigger code. The inverse of OBJECT_ID.

CREATE PROCEDURE GetObjName
AS SELECT OBJECT_NAME(22)
GO
Copy code to clipboard

PATINDEX

PATINDEX(pattern,string)

Returns an integer specifying the beginning position of the first occurrence of pattern in string, counting from 1. If pattern is not found in string, 0 is returned. Specify pattern as a quoted string. Comparisons are case-sensitive. The pattern can contain the following wildcard characters:

% Zero or more characters. For example, '%a%' returns the position of the first occurrence of 'a' in string, including 'a' as the first character in string.
_ Any single character. For example, '_l%' returns 1 if string begins with a substring such as 'Al', 'el', and 'il'.
[xyz] Any single character from the specified list of characters. For example, '[ai]l%' returns 1 if string begins with the substring 'al' or 'il', but not 'el' or 'Al'.
[a-z] Any single character from the specified range of characters. For example, '%s[a-z]t%' matches 'sat', 'set', and 'sit'. A range must be specified in ascending ASCII sequence.

The caret (^) character is a not a wildcard character; if included within square brackets it is treated as a literal. A pattern commonly consists of a search string enclosed in percent (%) characters '%Chicago%' indicating that the entire string should be searched.

PATINDEX is supported for sharded, parallel, and linked table queries.

PI

PI()

Returns the constant pi. The parentheses are required; no argument is permitted. Thus PI() returns 3.141592653589793238.

POWER

POWER(num,exponent)

Returns the value num raised to exponent.

QUOTENAME

QUOTENAME(value)

Returns value as a delimited identifier. TSQL supports double quotes ("value") as delimiter characters. For example:

PRINT 123
  // returns 123
PRINT QUOTENAME(123)
  // returns "123"
Copy code to clipboard

RADIANS

RADIANS(float)

Converts an angle measurement in degrees to the corresponding measurement in radians.

RAND

RAND([seed])

Returns a random number as a fractional number less than 1. The optional seed integer argument is ignored; it is provided for compatibility. If RAND is used more than once in a query it returns different random values.

REPLACE

REPLACE(target,search,replace)

Finds every instance of the search string in the target string and replaces it with the replace string, and returns the resulting string. To remove the search string from the target string, specify replace as an empty string.

REPLICATE

REPLICATE(expression,repeat-count)

REPLICATE returns a string of repeat-count instances of expression, concatenated together.

If expression is NULL, REPLICATE returns NULL. If expression is the empty string, REPLICATE returns an empty string.

If repeat-count is a fractional number, only the integer part is used. If repeat-count is 0, REPLICATE returns an empty string. If repeat-count is a negative number, NULL, or a non-numeric string, REPLICATE returns NULL.

REVERSE

REVERSE(string)

Reverses the order of the characters in string.

RIGHT

RIGHT(string,int)

Returns int number of characters from string, counting from the right. If int is larger than string, the full string is returned. See LEFT.

ROUND

ROUND(num,length)

Returns num rounded to the number of decimal digits specified by the integer length. If length is greater than the number of decimal digits, no rounding is performed. If length is 0, num is rounded to an integer. If the length argument is omitted, it defaults to 0. If length is a negative integer, num is rounded to the left of the decimal point. A third argument is not accepted by ROUND.

RTRIM

RTRIM(string)

Removes trailing blanks from string.

If string consists entirely of blank spaces, the dialect determines behavior:

  • Sybase: returns NULL.

  • MSSQL: returns the empty string.

See LTRIM.

SCOPE_IDENTITY

Returns the last identity value inserted into an IDENTITY column in the same scope. However, the last IDENTITY is not limited to the scope of the current procedure. Therefore, you should only use SCOPE_IDENTITY when you know that a statement within the current procedure has generated an IDENTITY value. For example, SCOPE_IDENTITY should be used after an INSERT command in the same procedure.

The following Dynamic SQL example returns the IDENTITY value from the second INSERT:

  SET sql=6
  SET sql(1)="CREATE TABLE #mytest (MyId INT IDENTITY(1,1),"
  SET sql(2)="Name VARCHAR(20))"
  SET sql(3)="INSERT INTO #mytest(Name) VALUES ('John Smith')"
  SET sql(4)="INSERT INTO #mytest(Name) VALUES ('Walter Jones')"
  SET sql(5)="PRINT SCOPE_IDENTITY()"
  SET sql(6)="DROP TABLE #mytest"
  SET statement=##class(%SQL.Statement).%New()
  SET statement.%Dialect="MSSQL"
  SET status=statement.%Prepare(.sql)
  SET result=statement.%Execute()
  DO result.%Display()
Copy code to clipboard

SIGN

SIGN(num)

Returns a value indicating the sign of num. If num is negative (for example, -32), it returns -1. If num is positive (for example, 32 or +32), it returns 1. If num is zero (for example, 0 or -0), it returns 0.

SIN

SIN(float)

Sine: returns the sine of the angle specified in float. Thus 1 returns .841470984807...

SPACE

SPACE(num)

Returns a string of blank spaces of length num.

SQRT

SQRT(num)

Returns the square root of num. Thus SQRT(9) returns 3.

SQUARE

SQUARE(num)

Returns the square of num. Thus SQUARE(9) returns 81.

STR

STR(num,[length[,precision]])

Returns a string of length characters. If the integer length is equal to or greater than the number of characters in the numeric num (including decimal point and sign characters), STR returns num converted to a string and padded with leading blanks to make the resulting string of length characters.

If the optional integer precision is specified, num is truncated to the specified number of decimal digits before string conversion. If precision is omitted, num is truncated to its integer portion. If precision is larger than the number of decimal digits, num is padded with trailing zeros before string conversion.

If length is omitted, it defaults to 10. If length is less than the number of characters in num (after adjustment by precision) a dummy string consisting of all asterisks of length number of characters is returned.

STUFF

STUFF(string,start,length,replace)

Returns string with length number of characters removed and the replace string inserted. The point of removal and insertion is specified by the start integer, counting from the beginning of string. If length is 0, no characters are removed. If replace is the empty string, no characters are inserted.

If start is greater than the number of characters in string, no value is returned. If start is 1, length number of characters are removed from the beginning of string and the replace string inserted. If start is 0, length minus 1 number of characters are removed from the beginning of string and the replace string inserted.

If length is greater than or equal to the number of characters in string, the replace string is returned. The replace string length is not limited by the length of string or length.

SUBSTRING

SUBSTRING(string,start,length)

Returns a substring of string beginning at the location start for the length number of characters. If start is greater than the length of string, or if length is 0, no string is returned.

SUM

SUM(numfield) 
SUM(DISTINCT numfield)

Aggregate function: used in a query to return the sum of the values in the numfield column. For example:

SELECT SUM(Age) FROM Sample.Person
Copy code to clipboard

SUM(DISTINCT numfield) sums the unique values in the numfield column. Fields with NULL are ignored.

SUSER_NAME

SUSER_NAME()

Returns the name of the current OS user. Parentheses are required, no argument is permitted. Equivalent to TSQL USER_NAME(), the InterSystems SQL USER function, and the ObjectScript $USERNAME special variable.

SUSER_SNAME

SUSER_SNAME()

Returns the name of the current OS user. Parentheses are required, no argument is permitted. Equivalent to TSQL USER_NAME(), the InterSystems SQL USER function, and the ObjectScript $USERNAME special variable.

TAN

TAN(float)

Tangent: returns the tangent of the angle specified in float. Thus 1 returns 1.55740772465...

TEXTPTR

TEXTPTR(field)

Returns an internal pointer to the image or text column data specified in field. The data type of this pointer is VARBINARY(16).

TEXTVALID

TEXTVALID('table.field',textpointer)

Takes an internal pointer to an image or text column from TEXTPTR, and compares it to a specified in table.field. Returns 1 if the pointer points to the specified table.field. Otherwise, returns 0.

UNICODE

UNICODE(char)

Returns the Unicode integer value corresponding to the first character in the string char. Thus, UNICODE('A') returns 65.

UNICODE is functionally identical to ASCII. The reverse of this function is CHAR.

UPPER

UPPER(string)

Returns string with all lowercase letters converted to uppercase. See LOWER.

USER

USER

Returns the name of the current user.

Note that no parentheses are used with this function.

USER_NAME

USER_NAME([userid])

Returns the name of the user specified by user ID. If the optional userid is omitted, returns the name of the current user. The argument is optional; the parentheses are mandatory.

YEAR

YEAR(date)

Returns the year portion of the specified date or date/time string. The date can be specified in ODBC timestamp format:

yyyy-mm-dd hh:mm:ss.n

The date separator can be either a hyphen (-) or a slash (/).

The date can also be specified in InterSystems IRIS $HOROLOG date format, such as 60703 (March 14, 2007).

Unsupported Functions

The following Microsoft Transact-SQL functions are not supported by TSQL at this time: APP_NAME, ATN2, BINARY_CHECKSUM, CHECKSUM, COL_LENGTH, COLLATIONPROPERTY, COLUMNPROPERTY, CURSOR_STATUS, DATABASEPROPERTY, DATABASEPROPERTYEX, DB_ID, DIFFERENCE, FILE_ID, FILE_NAME, FILEGROUP_ID, FILEGROUP_NAME, FILEGROUPPROPERTY, FILEPROPERTY, FORMATMESSAGE, FULLTEXTCATALOGPROPERTY, FULLTEXTSERVICEPROPERTY, GETANSINULL, HOST_ID, IDENT_CURRENT, IDENT_INCR, IDENT_SEED, IDENTITY, INDEXKEY_PROPERTY, INDEXPROPERTY, ISDATE, IS_MEMBER, IS_SRVROLEMEMBER, OBJECTPROPERTY, PARSENAME, PERMISSIONS, ROWCOUNT_BIG, SERVERPROPERTY, SESSIONPROPERTY, SESSION_USER, SOUNDEX, SQL_VARIANT_PROPERTY, STATS_DATE, STDEV, STDEVP, SYSTEM_USER, TYPEPROPERTY.