Built-in functions

Aggregate

TransactSQL	Snowflake	Notes
APPROX_COUNT_DISTINCT	APPROX_COUNT_DISTINCT
AVG​	AVG
CHECKSUM_AGG	*to be defined
COUNT	COUNT
COUNT_BIG	*to be defined
GROUPING	GROUPING
GROUPING_ID	GROUPING_ID
MAX	MAX
MIN	MIN
STDEV	STDDEV, STDEV_SAMP
STDEVP	STDDEV_POP
SUM	SUM
VAR	VAR_SAMP
VARP	VAR_POP​

Analytic

TransactSQL	Snowflake	Notes
CUME_DIST	CUME_DIST
FIRST_VALUE	FIRST_VALUE
LAG	LAG
LAST_VALUE	LAST_VALUE
LEAD	LEAD
PERCENTILE_CONT	PERCENTILE_CONT
PERCENTILE_DISC	PERCENTILE_DISC
PERCENT_RANK	PERCENT_RANK

Collation

TransactSQL	Snowflake	Notes
COLLATIONPROPERTY	*to be defined
TERTIARY_WEIGHTS	*to be defined

Configuration

TransactSQL	Snowflake	Notes
​@@DBTS	*to be defined
@@LANGID	*to be defined
@@LANGUAGE	*to be defined
@@LOCK_TIMEOUT	*to be defined
@@MAX_CONNECTIONS	*to be defined
@@MAX_PRECISION	*to be defined
@@NESTLEVEL	*to be defined
@@OPTIONS	*to be defined
@@REMSERVER	*to be defined
@@SERVERNAME	CONCAT('app.snowflake.com', CURRENT_ACCOUNT( ))
@@SERVICENAME	*to be defined
@@SPID	*to be defined
@@TEXTSIZE	*to be defined
@@VERSION	*to be defined	Can be mimicked by using CURRENT_VERSION

Conversion

TransactSQL	Snowflake	Notes
CAST	CAST	Returns NULL if the value isn't a number, otherwise returns the numeric value as its. When using operators such as <, >, =, <> then must be follow by a NULL
CONVERT	Check CONVERT	Same behavior as CAST
PARSE	*to be defined
TRY_CAST	TRY_CAST	Returns NULL if the value isn't a number, otherwise returns the numeric value as its. When using operators such as <, >, =, <> then must be follow by a NULL
TRY_CONVERT	*to be defined	Same behavior as TRY_CAST
TRY_PARSE	TRY_CAST	Behavior may be different when parsing an integer as date or timestamp.

Cryptographic

TransactSQL	Snowflake	Notes
ASYMKEY_ID	*to be defined
ASYMKEYPROPERTY	*to be defined
CERTENCODED	*to be defined
CERTPRIVATEKEY	*to be defined
DECRYPTBYASYMKEY	*to be defined
DECRYPTBYCERT	*to be defined
DECRYPTBYKEY	*to be defined
DECRYPTBYKEYAUTOASYMKEY	*to be defined
DECRYPTBYKEYAUTOCERT	*to be defined
DECRYPTBYPASSPHRASE	*to be defined​	Can be mimicked by using DENCRYPT_RAW
ENCRYPTBYASYMKEY	*to be defined
ENCRYPTBYCERT	*to be defined
ENCRYPTBYKEY	*to be defined
ENCRYPTBYPASSPHRASE	*to be defined	Can be mimicked by using ENCRYPT_RAW
HASHBYTES	MD5, SHA1, SHA2	Currently only supported separated hash. Use proper one according to the required algorithm MD5, is a 32-character hex-encoded SHA1, has a 40-character hex-encoded string containing the 160-bit SHA2, a hex-encoded string containing the N-bit SHA-2 message digest. Sizes are: 224 = SHA-224 256 = SHA-256 (Default) 384 = SHA-384 512 = SHA-512
IS_OBJECTSIGNED	*to be defined
KEY_GUID	*to be defined
KEY_ID	*to be defined
KEY_NAME	*to be defined
SIGNBYASYMKEY	*to be defined
SIGNBYCERT	*to be defined
SYMKEYPROPERTY	*to be defined
VERIGYSIGNEDBYCERT	*to be defined

Cursor

TransactSQL	Snowflake	Notes
@@CURSOR_ROWS	*to be defined	​
@@FETCH_STATUS	*to be defined
CURSOR_STATUS	*to be defined

Data type

TransactSQL	Snowflake	Notes
DATALENGTH	OCTET_LENGTH	​Snowflake doesn’t use fractional bytes so length is always calculated as 8 * OCTET_LENGTH
IDENT_SEED	*to be defined
IDENT_CURRENT	*to be defined
IDENTITY	*to be defined
IDENT_INCR	*to be defined
SQL_VARIANT_PROPERTY	*to be defined

Date & Time

TransactSQL	Snowflake	Notes
@@DATEFIRST	*to be defined
@@LANGUAGE	*to be defined
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP
CURRENT_TIMEZONE	*to be defined
DATEADD	DATEADD
DATEDIFF	DATEDIFF
DATEDIFF_BIG	*to be defined
DATEFROMPARTS	DATE_FROM_PARTS
DATENAME	*to be defined	This function receives two arguments: a datepart and date. It returns a string. Here are the supported dateparts from TSQL to Snowflake year, yyyy, yy -> DATE_PART(YEAR, "$date") quarter, qq, q -> DATE_PART(QUARTER, "$date") month, mm, m -> MONTHNAME( "$date"), thou only providing a three-letter english month name dayofyear, dy, y -> DATE_PART(DAYOFYEAR, "$date") day, dd, d -> DATE_PART(DAY, "$date") week, wk, ww -> DATE_PART(WEEK, "$date") weekday, dw -> DAYNAME("$date"), thou only providing an three-letter english day name hour, hh -> DATE_PART(HOUR, "$date") minute, n -> DATE_PART(MINUTE, "$date") second, ss, s -> DATE_PART(SECOND, "$date") millisecond, ms -> DATE_PART(MS, "$date") microsecond, mcs -> DATE_PART(US, "$date") nanosecond, ns -> DATE_PART(NS, "$date") TZoffset, tz -> needs a special implementation to get the time offset
DATEPART	DATE_PART
DATETIME2FROMPARTS	*to be defined
DATETIMEFROMPARTS	*to be defined	​Can be mimicked by using a combination of DATE_FROM_PARTS and TIME_FROM_PARTS
DATETIMEOFFSETFROMPARTS	*to be defined
DAY	DAY
EOMONTH	*to be defined	Can be mimicked by using LAST_DAY
FORMAT	*to be defined	Maps to TO_CHAR
GETDATE	GETDATE
GETUTCDATE	*to be defined	Can be mimicked by using CONVERT_TIMEZONE
ISDATE	*to be defined	Can be mimicked by using TRY_TO_DATE Returns NULL if the value isn't a date, otherwise returns the date value as its. When using operators such as <, >, =, <> then must be follow by a NULL
MONTH	MONTH
SMALLDATETIMEFROMPARTS	*to be defined	​​Can be mimicked by using a combination of DATE_FROM_PARTS and TIME_FROM_PARTS
SWITCHOFFSET	*to be defined	​Can be mimicked by using CONVERT_TIMEZONE
SYSDATETIME	LOCALTIME
SYSDATETIMEOFFSET	*to be defined	​Can be mimicked by using CONVERT_TIMEZONE and LOCALTIME
SYSUTCDATETIME	*to be defined	​​Can be mimicked by using CONVERT_TIMEZONE and LOCALTIME
TIMEFROMPARTS	TIME_FROM_PARTS	​
TODATETIMEOFFSET	*to be defined	​Can be mimicked by using CONVERT_TIMEZONE
YEAR	YEAR

JSON

TransactSQL	Snowflake	Notes
ISJSON	CHECK_JSON	​This is a 'preview feature' in Snowflake
JSON_VALUE	*to be defined	Can be mimic by using TO_VARCHAR(GET_PATH(PARSE_JSON(JSON), PATH))
JSON_QUERY	*to be defined
JSON_MODIFY	*to be defined

Mathematical

TransactSQL	Snowflake	Notes
ABS	ABS
ACOS	ACOS
ASIN	ASIN
ATAN	ATAN
ATN2	ATAN2
CEILING	CEIL
COS	COS
COT	COT
DEGREES	DEGREES
EXP	EXP
FLOOR	FLOOR
LOG	LN
LOG10	LOG
PI	PI
POWER	POWER
RADIANS	RADIANS
RAND	RANDOM
ROUND	ROUND
SIGN	SIGN
SIN	SIN
SQRT	SQRT
SQUARE	SQUARE

Logical

TransactSQL	Snowflake	Notes
CHOOSE	*to be defined	Can be mimic by using DECODE
GREATEST	GREATEST
IIF	IIF
LEAST	LEAST
NULLIF	NULLIF

Metadata

TransactSQL	Snowflake	Notes
@@PROCID	*to be defined
APP_NAME	*to be defined
APPLOCK_MODE	*to be defined
APPLOCK_TEST	*to be defined
ASSEMBLYPROPERTY	*to be defined
COL_LENGTH	*to be defined
COL_NAME	*to be defined
COLUMNPROPERTY	*to be defined
DATABASE_PRINCIPAL_ID	*to be defined	Maps to CURRENT_USER when no args
DATABASEPROPERTYEX	*to be defined
DB_ID	*to be defined	We recommend changing to CURRENT_DATABASE(). If there is a need to emulate this functionality. SELECT DATE_PART(EPOCH,CREATED) FROM INFORMATION_SCHEMA.DATABASES WHERE DATABASE_NAME = 'DB' ; Can achieve something similar
DB_NAME	*to be defined	Mostly used in the procedurename mentioned above
FILE_ID	*to be defined
FILE_IDEX	*to be defined
FILE_NAME	*to be defined
FILEGROUP_ID	*to be defined
FILEGROUP_NAME	*to be defined
FILEGROUPPROPERTY	*to be defined
FILEPROPERTY	*to be defined
FULLTEXTCATALOGPROPERTY	*to be defined
FULLTEXTSERVICEPROPERTY	*to be defined
INDEX_COL	*to be defined
INDEXKEY_PROPERTY	*to be defined
INDEXPROPERTY	*to be defined
NEXT VALUE FOR	*to be defined
OBJECT_DEFINITION	*to be defined
OBJECT_ID	*to be defined	In most cases can be replaced. Most cases are like: IF OBJECT_ID('dbo.TABLE') IS NOT NULL DROP TABLE dbo.Table which can be replaced by a DROP TABLE IF EXISTS (this syntax is also supported in SQL SERVER). If the object_id needs to be replicated, a UDF is added depending on the second parameter of the function call.
OBJECT_NAME	*to be defined	Can be replaced by: CREATE OR REPLACE PROCEDURE FOO() RETURNS STRING LANGUAGE JAVASCRIPT AS ' var rs = snowflake.execute({sqlText:SELECT CURRENT_DATABASE() || '.' || ?, binds:[arguments.callee.name]}); rs.next(); var procname = rs.getColumnValue(1); return procname; ';
OBJECT_NAME(@@PROCID)	'ObjectName'	This transformation only occurs when it is inside a DeclareStatement. ObjectName is the name of the TopLevelObject that contains the Function.
OBJECT_SCHEMA_NAME	*to be defined
OBJECT_SCHEMA_NAME(@@PROCID)	:OBJECT_SCHEMA_NAME	This transformation only occurs when it is inside a DeclareStatement.
OBJECTPROPERTY	*to be defined
OBJECTPROPERTYEX	*to be defined
ORIGINAL_DB_NAME	*to be defined
PARSENAME	PARSE_NAME_UDF	It creates a UDF to emulate the same behavior of Parsename function.
*to be defined
SCHEMA_NAME	*to be defined
SCOPE_IDENTITY	*to be defined	It this is needed I would recommend to use sequences, and capture the value before insert
SERVERPROPERTY	*to be defined
STATS_DATE	*to be defined
TYPE_ID	*to be defined
TYPE_NAME	*to be defined
TYPEPROPERTY	*to be defined
VERSION	*to be defined

Ranking

TransactSQL	Snowflake	Notes
DENSE_RANK	DENSE_RANK
NTILE	NTILE
RANK	RANK
ROW_NUMBER	ROW_NUMBER

Replication

TransactSQL	Snowflake	Notes
PUBLISHINGSERVERNAME	*to be defined

Rowset

TransactSQL	Snowflake	Notes
OPENDATASOURCE	*to be defined
OPENJSON	*to be defined
QPENQUERY	*to be defined
OPENROWSET	*to be defined
OPENXML	OPENXML_UDF	User-defined function used as a equivalent behavior in Snowflake.

Security

TransactSQL	Snowflake	Notes
CERTENCODED	*to be defined
CERTPRIVATEKEY	*to be defined
CURRENT_USER	CURRENT_USER
DATABASE_PRINCIPAL_ID	*to be defined
HAS_PERMS_BY_NAME	*to be defined
IS_MEMBER	*to be defined	Change to query INFORMATION_SCHEMA although the client might require defining new roles
IS_ROLEMEMBER	*to be defined	Snowflake's a similar function IS_ROLE_IN_SESSION
IS_SRVROLEMEMBER	*to be defined
LOGINPROPERTY	*to be defined
ORIGINAL_LOGIN	*to be defined
PERMISSIONS	*to be defined
PWDCOMPARE	*to be defined
PWDENCRYPT	*to be defined
SCHEMA_ID	*to be defined
SCHEMA_NAME	*to be defined
SESSION_USER	*to be defined
SUSER_ID	*to be defined
SUSER_NAME	*to be defined
SUSER_SID	*to be defined
SUSER_SNAME	*to be defined
sys.fn_builtin_permissions	*to be defined
sys.fn_get_audit_file	*to be defined
sys.fn_my_permissions	*to be defined
SYSTEM_USER	*to be defined
USER_ID	*to be defined
USER_NAME	*to be defined	Maps to CURRENT_USER

String

TransactSQL	Snowflake	Notes
ASCII	ASCII
CHAR	CHR, CHAR
CHARINDEX	CHARINDEX
CONCAT	CONCAT
CONCAT_WS	CONCAT_WS
COALESCE	COALESCE
DIFFERENCE	*to be defined
FORMAT	*to be defined
LEFT	LEFT
LEN	LEN
LOWER	LOWER
LTRIM	LTRIM
NCHAR	*to be defined
PATINDEX	PAT_INDEX_UDF	Map to REGEXP_INSTR
QUOTENAME	QUOTE_NAME_UDF	It creates a UDF to emulate the same behavior of Quotename function
REPLACE	REPLACE
REPLICATE	REPEAT
REVERSE	REVERSE
RIGHT	RIGHT
RTRIM	RTRIM
SOUNDEX	SOUNDEX
SPACE	*to be defined
STR	*to be defined
STRING_AGG	*to be defined
STRING_ESCAPE	*to be defined
STRING_SPLIT	SPLIT_TO_TABLE
STUFF	*to be defined	CREATE OR REPLACE FUNCTION STUFF(S string, STARTPOS int, LENGTH int, NEWSTRING string) RETURNS string LANGUAGE SQL AS ' left(S, STARTPOS) || NEWSTRING || substr(S, STARTPOS + LENGTH + 1) ';
SUBSTRING	SUBSTRING
TRANSLATE	TRANSLATE
TRIM	TRIM
UNICODE	UNICODE
UPPER	UPPER

System

TransactSQL	Snowflake	Notes
$PARTITION	*to be defined
@@ERROR	*to be defined
@@IDENTITY	*to be defined	It this is needed I would recommend to use sequences, and capture the value before insert
@@PACK_RECEIVED	*to be defined
@@ROWCOUNT	*to be defined
@@TRANCOUNT	*to be defined
BINARY_CHECKSUM	*to be defined
CHECKSUM	*to be defined
COMPRESS	COMPRESS	​Snowflake's version has a method argument to indicate the compression method. These are the valid values: SNAPPY, ZLIB, ZSTD, BZ2 The compression level is specified in parentheses and must be a non-negative integer
CONNECTIONPROPERTY	*to be defined
CONTEXT_INFO	*to be defined
CURRENT_REQUEST_ID	*to be defined
CURRENT_TRANSACTION_ID	*to be defined
DECOMPRESS	*to be defined	Snowflake has two functions for these: DECOMPRESS_BINARY and DECOMPRESS_STRING​
ERROR_LINE	*to be defined	Will map to ERROR_LINE helper. EXEC helper will capture the Exception line property from the stack trace.
ERROR_MESSAGE	SQLERRM
ERROR_NUMBER	*to be defined	Will map to ERROR_NUMBER helper. EXEC helper will capture the Exception code property.
ERROR_PROCEDURE	*to be defined	Will map to ERROR_PROCEDURE helper, taken from the arguments.callee.name procedure property
ERROR_SEVERITY	*to be defined
ERROR_STATE	*to be defined	Helper will capture Exception state property
FORMATMESSAGE	FORMATEMESSAGE_UDF	It creates a UDF to emulate the same behavior of FORMATMESSAGE function but with some limitations.
GET_FILESTREAM_TRANSACTION_CONTEXT	*to be defined
GETANSINULL	*to be defined
HOST_ID	*to be defined
HOST_NAME	*to be defined
ISNULL	NVL
ISNUMERIC	IS_NUMERIC_UDF	No direct equivalent but can be mapped to a custom UDF, returning the same values as in TSQL.
MIN_ACTIVE_ROWVERSION	*to be defined	​
NEWID	*to be defined	​Maps to UUID_STRING
NEWSEQUENTIALID	*to be defined	​
ROWCOUNT_BIG	*to be defined	​
SESSION_CONTEXT	*to be defined	​
SESSION_ID	*to be defined	​
XACT_STATE	*to be defined	​

System Statistical

TransactSql	Snowflake	Notes
@@CONNECTIONS	*to be defined	​Snowflake's a similar function: LOGIN_HISTORY. Returns login events within a specified time range
@@PACK_RECEIVED	*to be defined
@@CPU_BUSY	*to be defined
@@PACK_SENT	*to be defined
@@TIMETICKS	*to be defined
@@IDLE	*to be defined
@@TOTAL_ERRORS	*to be defined
@@IO_BUSY	*to be defined
@@TOTAL_READ	*to be defined
@@PACKET_ERRORS	*to be defined
@@TOTAL_WRITE	*to be defined

Text & Image

TransactSQL	Snowflake	Notes
TEXTPTR	*to be defined
TEXTVALID	*to be defined

Trigger

TransactSQL	Snowflake	Notes
COLUMNS_UPDATED	*to be defined
EVENTDATA	*to be defined
TRIGGER_NESTLEVEL	*to be defined
UPDATE	*to be defined