SnowConvert: Teradataの関数参照¶

PUBLIC.QUARTERNUMBER_OF_YEAR_UDF(INPUT TIMESTAMP_TZ)

SELECT PUBLIC.QUARTERNUMBER_OF_YEAR_UDF(DATE '2022-01-01'),
PUBLIC.QUARTERNUMBER_OF_YEAR_UDF(DATE '2025-12-31');

4, 1

PUBLIC.DAYNUMBER_OF_YEAR_UDF(INPUT TIMESTAMP_TZ)

SELECT DAYNUMBER_OF_YEAR(CURRENT_DATE,'ISO');

SELECT
PUBLIC.DAYNUMBER_OF_YEAR_UDF(CURRENT_DATE());

PUBLIC.SUBSTR_UDF(BASE_EXPRESSION STRING, START_POSITION FLOAT)

SELECT SUBSTRING('Hello World!' FROM -2);

SELECT
PUBLIC.SUBSTR_UDF('Hello World!', -2);

PUBLIC.CHKNUM_UDF(NUM STRING);

SELECT CHKNUM('1032');

SELECT
PUBLIC.CHKNUM_UDF('1032');

PUBLIC.TD_YEAR_END_UDF(INPUT date)

SELECT  PUBLIC.TD_YEAR_END_UDF(DATE '2022-01-01'),
PUBLIC.TD_YEAR_END_UDF(DATE '2022-04-12');

2022-12-31, 2022-12-31

PERIOD_OVERLAPS_UDF(PERIODS ARRAY)

SELECT
	PERIOD(DATE '2009-01-01', DATE '2010-09-24')
	OVERLAPS
	PERIOD(DATE '2009-02-01', DATE '2009-06-24');

SELECT
	PUBLIC.PERIOD_OVERLAPS_UDF(ARRAY_CONSTRUCT(PUBLIC.PERIOD_UDF(DATE '2009-01-01', DATE '2010-09-24') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!, PUBLIC.PERIOD_UDF(DATE '2009-02-01', DATE '2009-06-24') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!)) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!;

PUBLIC.WEEK_NUMBER_OF_QUARTER_COMPATIBLE_UDF(INPUT TIMESTAMP_TZ)

SELECT WEEK_NUMBER_OF_QUARTER_COMPATIBLE_UDF(DATE '2022-05-01', 'COMPATIBLE'),
WEEK_NUMBER_OF_QUARTER_COMPATIBLE_UDF(DATE '2022-07-06', 'COMPATIBLE')

5, 1

PUBLIC.ROMAN_NUMERALS_MONTH_UDF(INPUT TIMESTAMP_TZ)

SELECT PUBLIC.ROMAN_NUMERALS_MONTH_UDF(DATE '2021-10-26');

'X'

PUBLIC.TD_YEAR_BEGIN_UDF(INPUT DATE)

SELECT TD_YEAR_BEGIN(DATE '2022-01-01', 'COMPATIBLE'),
TD_YEAR_BEGIN(DATE '2022-04-12');

2022-01-01, 2022-01-01

PUBLIC.FULL_MONTH_NAME_UDF(INPUT TIMESTAMP_TZ, RESULTCASE VARCHAR)

SELECT PUBLIC.FULL_MONTH_NAME_UDF(DATE '2021-10-26', 'uppercase');
SELECT PUBLIC.FULL_MONTH_NAME_UDF(DATE '2021-10-26', 'lowercase');
SELECT PUBLIC.FULL_MONTH_NAME_UDF(DATE '2021-10-26', 'firstOnly');

OCTOBER
october
October

TO_BYTES_HEX_UDF(INPUT FLOAT)

SELECT TO_BYTES_HEX_UDF('448');

01c0

PERIOD_INTERSECT_UDF(PERIODS ARRAY)

SELECT
	PERIOD(DATE '2009-01-01', DATE '2010-09-24')
	P_INTERSECT
	PERIOD(DATE '2009-02-01', DATE '2009-06-24');

SELECT
	PUBLIC.PERIOD_INTERSECT_UDF(ARRAY_CONSTRUCT(PUBLIC.PERIOD_UDF(DATE '2009-01-01', DATE '2010-09-24') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!, PUBLIC.PERIOD_UDF(DATE '2009-02-01', DATE '2009-06-24') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!)) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!;

PUBLIC.INTERVAL_TO_SECONDS_UDF
(INPUT_PART VARCHAR(30), INPUT_VALUE VARCHAR())

PUBLIC.TIMESTAMP_ADD_UDF(FIRST_DATE TIMESTAMP_LTZ, SECOND_DATE TIMESTAMP_LTZ)

PUBLIC.INTERVAL_MULTIPLY_UDF
(INPUT_PART VARCHAR(30), INPUT_VALUE VARCHAR(), INPUT_MULT INTEGER)

SELECT INTERVAL '6-10' YEAR TO MONTH * 8;

SELECT
PUBLIC.INTERVAL_MULTIPLY_UDF('YEAR TO MONTH', '6-10', 8);

PUBLIC.TD_DAY_OF_WEEK_UDF(INPUT TIMESTAMP_TZ)

SELECT td_day_of_week(DATE '2022-03-02');

SELECT
PUBLIC.TD_DAY_OF_WEEK_UDF(DATE '2022-03-02');

PUBLIC.ISO_YEAR_PART_UDF(INPUT TIMESTAMP_TZ, DIGITS INTEGER)

SELECT PUBLIC.ISO_YEAR_PART_UDF(DATE '2021-10-26', 3);
SELECT PUBLIC.ISO_YEAR_PART_UDF(DATE '2021-10-26', 2);
SELECT PUBLIC.ISO_YEAR_PART_UDF(DATE '2021-10-26', 1);

'021'
'21'
'1'

PUBLIC.DIFF_TIME_PERIOD_UDF(TIME STRING, PERIOD VARCHAR(50))

SELECT DIFF_TIME_PERIOD_UDF('SECONDS','2022-11-26 10:15:20.000*2022-11-26 10:15:25.000');

5

PUBLIC.WEEK_NUMBER_OF_QUARTER_ISO_UDF(INPUT TIMESTAMP_TZ)

SELECT WEEKNUMBER_OF_QUARTER(DATE '2022-05-01', 'ISO'),
WEEKNUMBER_OF_QUARTER(DATE '2022-07-06', 'ISO')

SELECT
PUBLIC.SUBSTR_UDF('Hello World!', -2);

NVP_UDF(INSTRING VARCHAR, NAME_TO_SEARCH VARCHAR, NAME_DELIMITERS VARCHAR, VALUE_DELIMITERS VARCHAR, OCCURRENCE FLOAT)

SELECT PUBLIC.NVP_UDF('entree=-orange chicken&entree+.honey salmon', 'entree', '&', '=- +.', 1);

orange chicken

PUBLIC.MONTH_SHORT_UDF(INPUT TIMESTAMP_TZ, RESULTCASE VARCHAR)

SELECT PUBLIC.MONTH_SHORT_UDF(DATE '2021-10-26', 'uppercase');
SELECT PUBLIC.MONTH_SHORT_UDF(DATE '2021-10-26', 'lowercase');
SELECT PUBLIC.MONTH_SHORT_UDF(DATE '2021-10-26', 'firstOnly');

OCT
oct
Oct

PUBLIC.DATE_TO_INT_UDF(DATE_TO_CONVERT DATE)

SELECT mod(date '2015-11-26', 5890), sin(current_date);

CREATE TABLE SAMPLE_TABLE
(
    VARCHAR_TYPE VARCHAR,
    CHAR_TYPE CHAR(11),
    INTEGER_TYPE INTEGER,
    DATE_TYPE DATE,
    TIMESTAMP_TYPE TIMESTAMP,
    TIME_TYPE TIME,
    PERIOD_TYPE PERIOD(DATE)
);

REPLACE VIEW SAMPLE_VIEW
AS
SELECT
CAST(DATE_TYPE AS SMALLINT),
CAST(DATE_TYPE AS DECIMAL),
CAST(DATE_TYPE AS NUMBER),
CAST(DATE_TYPE AS FLOAT),
CAST(DATE_TYPE AS INTEGER)
FROM SAMPLE_TABLE;

SELECT
mod(PUBLIC.DATE_TO_INT_UDF(date '2015-11-26'), 5890),
sin(PUBLIC.DATE_TO_INT_UDF(CURRENT_DATE()));

CREATE TABLE PUBLIC.SAMPLE_TABLE
(
    VARCHAR_TYPE VARCHAR,
    CHAR_TYPE CHAR(11),
    INTEGER_TYPE INTEGER,
    DATE_TYPE DATE,
    TIMESTAMP_TYPE TIMESTAMP,
    TIME_TYPE TIME,
    PERIOD_TYPE VARCHAR(24) COMMENT 'PERIOD(DATE)' /*** MSC-WARNING - MSCEWI1036 - PERIOD DATA TYPE "PERIOD(DATE)" CONVERTED TO VARCHAR ***/
);

CREATE OR REPLACE VIEW PUBLIC.SAMPLE_VIEW
AS
SELECT
PUBLIC.DATE_TO_INT_UDF(DATE_TYPE),
PUBLIC.DATE_TO_INT_UDF(DATE_TYPE),
PUBLIC.DATE_TO_INT_UDF(DATE_TYPE),
PUBLIC.DATE_TO_INT_UDF(DATE_TYPE),
PUBLIC.DATE_TO_INT_UDF(DATE_TYPE)
FROM PUBLIC.SAMPLE_TABLE;

PERIOD_UDF(D1 TIMESTAMP_NTZ, D2 TIMESTAMP_NTZ)
PERIOD_UDF(D1 DATE, D2 DATE)
PERIOD_UDF(D1 TIME, D2 TIME)
PERIOD_UDF(D1 TIMESTAMP_NTZ, D2 TIMESTAMP_NTZ, PRECISIONDIGITS INT)
PERIOD_UDF(D1 TIME, D2 TIME, PRECISIONDIGITS INT)
PERIOD_UDF(D1 TIMESTAMP_NTZ)
PERIOD_UDF(D1 DATE)
PERIOD_UDF(D1 TIME)

SELECT
PERIOD_UDF('2005-02-03'),
PERIOD_UDF(date '2005-02-03'),
PERIOD_UDF(TIMESTAMP '2005-02-03 12:12:12.340000'),
PERIOD_UDF(TIMESTAMP '2005-02-03 12:12:12.340000');

2005-02-03*2005-02-04,
2005-02-03*2005-02-04,
2005-02-03 12:12:12.340000*2005-02-03 12:12:12.340001,
2005-02-03 12:12:12.340000*2005-02-03 12:12:12.340001

PUBLIC.DAYNAME_LONG_UDF(INPUT TIMESTAMP_TZ, RESULTCASE VARCHAR)

SELECT PUBLIC.DAYNAME_LONG_UDF(DATE '2021-10-26', 'uppercase');
SELECT PUBLIC.DAYNAME_LONG_UDF(DATE '2021-10-26', 'lowercase');
SELECT PUBLIC.DAYNAME_LONG_UDF(DATE '2021-10-26', 'firstOnly');

'TUESDAY'
'tuesday'
'Tuesday'

PUBLIC.TD_DAY_OF_WEEK_COMPATIBLE_UDF(INPUT TIMESTAMP_TZ)

SELECT PUBLIC.TD_DAY_OF_WEEK_COMPATIBLE_UDF(DATE '2022-01-01'),
PUBLIC.TD_DAY_OF_WEEK_COMPATIBLE_UDF(DATE '2023-05-05');

1, 6

PUBLIC.JAROWINKLER_UDF (string1 VARCHAR, string2 VARCHAR)

SELECT PUBLIC.JAROWINKLER_UDF('święta', 'swieta')

0.770000

PUBLIC.YEAR_BEGIN_ISO_UDF(INPUT DATE)

SELECT  PUBLIC.YEAR_BEGIN_ISO_UDF(DATE '2022-01-01'),
PUBLIC.YEAR_BEGIN_ISO_UDF(DATE '2022-04-12');

2021-01-04, 2022-01-03

PUBLIC.YEAR_PART_UDF(INPUT TIMESTAMP_TZ, DIGITS INTEGER)

SELECT PUBLIC.YEAR_PART_UDF(DATE '2021-10-26', 3);
SELECT PUBLIC.YEAR_PART_UDF(DATE '2021-10-26', 2);
SELECT PUBLIC.YEAR_PART_UDF(DATE '2021-10-26', 1);

'021'
'21'
'1'

PUBLIC.YEAR_WITH_COMMA_UDF(INPUT TIMESTAMP_TZ)

SELECT PUBLIC.YEAR_WITH_COMMA_UDF(DATE '2021-10-26');

'2,021'

MONTHS_BETWEEN_UDF(FIRST_DATE TIMESTAMP_LTZ, SECOND_DATE TIMESTAMP_LTZ)

SELECT MONTHS_BETWEEN_UDF('2022-02-14', '2021-02-14');

12

PUBLIC.SECONDS_PAST_MIDNIGHT_UDF(INPUT TIME)

SELECT PUBLIC.SECONDS_PAST_MIDNIGHT_UDF(TIME'10:30:45');

'37845'

PUBLIC.CHAR2HEXINT_UDF(INPUT_STRING VARCHAR);

SELECT CHAR2HEXINT('1234') from t1;

SELECT
PUBLIC.CHAR2HEXINT_UDF('1234') from
t1;

PUBLIC.INTERVAL_ADD_UDF
(INPUT_VALUE1 VARCHAR(), INPUT_PART1 VARCHAR(30), INPUT_VALUE2 VARCHAR(), INPUT_PART2 VARCHAR(30), OP CHAR, OUTPUT_PART VARCHAR())

CREATE TABLE INTERVAL_TABLE
(
    INTERVAL_YEAR_TYPE INTERVAL YEAR
);

SELECT INTERVAL_YEAR_TYPE - INTERVAL '7' MONTH FROM INTERVAL_TABLE;

CREATE OR REPLACE TABLE INTERVAL_TABLE
(
    INTERVAL_YEAR_TYPE VARCHAR(21) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL YEAR DATA TYPE CONVERTED TO VARCHAR ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

SELECT
    PUBLIC.INTERVAL_ADD_UDF(INTERVAL_YEAR_TYPE, 'YEAR', '7', 'MONTH', '-', 'YEAR TO MONTH')
    FROM
    INTERVAL_TABLE;

PUBLIC.DAY_OF_WEEK_LONG_UDF(INPUT_DATE TIMESTAMP)

PUBLIC.TD_WEEK_OF_CALENDAR_UDF(INPUT TIMESTAMP_TZ)

SELECT TD_WEEK_OF_CALENDAR(DATE '2023-11-30')

SELECT
PUBLIC.TD_WEEK_OF_CALENDAR_UDF(DATE '2023-11-30');

PUBLIC.WRAP_NEGATIVE_WITH_ANGLE_BRACKETS_UDF(INPUT NUMBER, FORMATARG VARCHAR)

SELECT PUBLIC.WRAP_NEGATIVE_WITH_ANGLE_BRACKETS_UDF(8456, '9999');
SELECT PUBLIC.WRAP_NEGATIVE_WITH_ANGLE_BRACKETS_UDF(-8456, '9999');

'8456'
'<8456>'

PUBLIC.INSTR_UDF(SOURCE_STRING STRING, SEARCH_STRING STRING)

SELECT INSTR_UDF('INSTR FUNCTION','N');

2

PUBLIC.TRANSLATE_CHK_UDF(COL_NAME STRING, SOURCE_REPERTOIRE_NAME STRING)

SELECT PUBLIC.TRANSLATE_CHK_UDF('ABC', 'UNICODE_TO_LATIN');

0

PUBLIC.EXPAND_ON_UDF(TIME STRING, SEQ NUMBER, PERIOD STRING)

SELECT bg FROM table1 EXPAND ON pd AS bg BY ANCHOR ANCHOR_SECOND;

WITH
ExpandOnCTE AS
(
SELECT
PUBLIC.EXPAND_ON_UDF('ANCHOR_SECOND', VALUE, pd) bg
FROM
table1,
TABLE(FLATTEN(PUBLIC.ROW_COUNT_UDF(PUBLIC.DIFF_TIME_PERIOD_UDF('ANCHOR_SECOND', pd))))
)
SELECT
bg
FROM
table1,
ExpandOnCTE;

PUBLIC.ROW_COUNT_UDF(NROWS DOUBLE)

SELECT ROW_COUNT_UDF(DIFFTTIME_PERIOD('SECONDS','2022-11-26 10:15:20.000*2022-11-26 10:15:25.000'));

[1, 2, 3, 4, 5]

SELECT NORMALIZE emp_id, duration FROM project EXPAND ON duration AS bg BY ANCHOR ANCHOR_SECOND;

WITH ExpandOnCTE AS
(
SELECT
    PUBLIC.EXPAND_ON_UDF('ANCHOR_SECOND', VALUE, duration) bg
FROM
    project,
TABLE(FLATTEN(PUBLIC.ROW_COUNT_UDF(PUBLIC.DIFF_TIME_PERIOD_UDF('ANCHOR_SECOND', duration))))
)
SELECT NORMALIZE emp_id,
    duration
FROM
    project,
    ExpandOnCTE;

PUBLIC.CENTURY_UDF(INPUT TIMESTAMP_TZ)

SELECT PUBLIC.CENTURY_UDF(DATE '1915-02-23');

'20'

PUBLIC.TIME_DIFFERENCE_UDF
(MINUEND TIME, SUBTRAHEND TIME, INPUT_PART VARCHAR)

select extract(day from (timestampColumn1 - timestampColumn2 day to hour)) from tableName;

SELECT
EXTRACT_TIMESTAMP_DIFFERENCE_UDF(timestampColumn1, timestampColumn2, 'DAY TO HOUR', 'DAY')
                                 from
tableName;

PUBLIC.INTERVAL_DIVIDE_UDF
(INPUT_PART VARCHAR(30), INPUT_VALUE VARCHAR(), INPUT_DIV INTEGER)

SELECT INTERVAL '6-10' YEAR TO MONTH / 8;

SELECT
PUBLIC.INTERVAL_DIVIDE_UDF('YEAR TO MONTH', '6-10', 8);

PUBLIC.DAYNUMBER_OF_MONTH_UDF(INPUT TIMESTAMP_TZ)

SELECT DAYNUMBER_OF_MONTH (DATE'2022-12-22', 'ISO');

SELECT
PUBLIC.DAYNUMBER_OF_MONTH_UDF(DATE'2022-12-22');

PUBLIC.LAST_DAY_DECEMBER_OF_ISO_UDF(INPUT TIMESTAMP_TZ)

SELECT PUBLIC.LAST_DAY_DECEMBER_OF_ISO_UDF(DATE '2022-01-01');

2021-12-31

PUBLIC.DATE_ADD_UDF(FIRST_DATE DATE, SECOND_DATE DATE)

SELECT
    CAST(CAST (COLUMNB AS DATE FORMAT 'MM/DD/YYYY') AS TIMESTAMP(0))
    +
    CAST (COLUMNA AS TIME(0) FORMAT 'HHMISS' )
FROM TIMEDIFF;

SELECT
    PUBLIC.DATEADD_UDF(CAST(CAST(COLUMNB AS DATE) !!!RESOLVE EWI!!! /*** SSC-EWI-0033 - FORMAT 'MM/DD/YYYY' REMOVED, SEMANTIC INFORMATION NOT FOUND. ***/!!! AS TIMESTAMP(0)), PUBLIC.TO_INTERVAL_UDF(CAST(COLUMNA AS TIME(0)) !!!RESOLVE EWI!!! /*** SSC-EWI-0033 - FORMAT 'HHMISS' REMOVED, SEMANTIC INFORMATION NOT FOUND. ***/!!!))
    FROM
    TIMEDIFF;

PUBLIC.JULIAN_TO_DATE_UDF(JULIAN_DATE CHAR(7))

SELECT JULIAN_TO_DATE_UDF('2022045');

'2022-02-14'

SELECT TO_DATE('2020002', 'YYYYDDD');

SELECT
PUBLIC.JULIAN_TO_DATE_UDF('2020002');

FUNCTION PUBLIC.FIRST_DAY_JANUARY_OF_ISO_UDF(INPUT TIMESTAMP_TZ)

SELECT PUBLIC.FIRST_DAY_JANUARY_OF_ISO_UDF(DATE '2022-01-01');

2021-01-01

PUBLIC.TIMESTAMP_DIFFERENCE_UDF
(MINUEND TIMESTAMP, SUBTRAHEND TIMESTAMP, INPUT_PART VARCHAR)

select (timestampColumn1 - timestampColumn2 YEAR) from tableName;

SELECT
(
PUBLIC.TIMESTAMP_DIFFERENCE_UDF(timestampColumn1, timestampColumn2, 'YEAR')) from
tableName;

PUBLIC.FIRST_DAY_OF_MONTH_ISO_UDF(YEAR NUMBER, MONTH NUMBER)

PUBLIC.INT_TO_DATE_UDF(NUMERIC_EXPRESSION INTEGER)

SELECT * FROM table1
WHERE date_column > 1011219

SELECT
* FROM
table1
WHERE date_column > PUBLIC.INT_TO_DATE_UDF( 1011219);

PUBLIC.NULLIFZERO_UDF(NUMBER_TO_VALIDATE NUMBER)

SELECT NULLIFZERO_UDF(0);

NULL

PUBLIC.DATE_LONG_UDF(INPUT TIMESTAMP_TZ)

SELECT PUBLIC.DATE_LONG_UDF(DATE '2021-10-26');

'Tuesday, October 26, 2021'

PUBLIC.TD_MONTH_OF_CALENDAR_UDF(INPUT TIMESTAMP_TZ)

SELECT TD_MONTH_OF_CALENDAR(DATE '2023-11-30')

SELECT
PUBLIC.TD_MONTH_OF_CALENDAR_UDF(DATE '2023-11-30');

PUBLIC.MONTH_NAME_LONG_UDF(INPUT_DATE TIMESTAMP)

PUBLIC.TD_DAY_OF_CALENDAR_UDF(INPUT TIMESTAMP_TZ)

SELECT td_day_of_calendar(current_date)

SELECT
PUBLIC.TD_DAY_OF_CALENDAR_UDF(CURRENT_DATE());

PERIOD_TO_TIME_UDF(PERIOD_VAL VARCHAR(22))

SELECT PERIOD_TO_TIME_UDF(PERIOD_UDF(CURRENT_TIME()));

08:42:04

PUBLIC.INSTR_UDF(SOURCE_STRING STRING, SEARCH_STRING STRING, POSITION DOUBLE, OCCURRENCE DOUBLE)

SELECT INSTR_UDF('CHOOSE A CHOCOLATE CHIP COOKIE','CH',2,2);

20

PUBLIC.ROUND_DATE_UDF(DATE_TO_ROUND TIMESTAMP_LTZ, UNIT_TO_ROUND_BY VARCHAR(5))

SELECT ROUND(CURRENT_DATE, 'RM') RND_DATE

SELECT
PUBLIC.ROUND_DATE_UDF(CURRENT_DATE(), 'RM') RND_DATE;

PUBLIC.SUBSTR_UDF(BASE_EXPRESSION STRING, START_POSITION FLOAT, LENGTH FLOAT)

SELECT 
    PUBLIC.SUBSTR_UDF('ABC', -1, 1),
    PUBLIC.SUBSTR_UDF('ABC', -1, 2),
    PUBLIC.SUBSTR_UDF('ABC', -1, 3),
    PUBLIC.SUBSTR_UDF('ABC', 0, 1),
    PUBLIC.SUBSTR_UDF('ABC', 0, 2);

'','','A','','A'

GETQUERYBANDVALUE_UDF(SEARCHNAME VARCHAR)

ALTER SESSION SET QUERY_TAG = 'user=Tyrone;role=security';
SELECT GETQUERYBANDVALUE_UDF('role');

security

SELECT GETQUERYBANDVALUE(1, 'group');

/** MSC-ERROR - MSCEWI2084 - TRANSACTION AND PROFILE LEVEL QUERY TAGS NOT SUPPORTED IN SNOWFLAKE, REFERENCING SESSION QUERY TAG INSTEAD **/
SELECT GETQUERYBANDVALUE_UDF('group');

PUBLIC.TD_WEEK_OF_YEAR_UDF(INPUT TIMESTAMP_TZ)

SELECT PUBLIC.WEEK_OF_YEAR_UDF(DATE '2024-05-10'),
PUBLIC.WEEK_OF_YEAR_UDF(DATE '2020-01-03')

18, 0

PUBLIC.EXTRACT_TIMESTAMP_DIFFERENCE_UDF
(MINUEND TIMESTAMP, SUBTRAHEND TIMESTAMP, INPUT_PART VARCHAR, EXTRACT_PART VARCHAR)

select extract(day from (timestampColumn1 - timestampColumn2 day to hour)) from tableName;

SELECT
EXTRACT_TIMESTAMP_DIFFERENCE_UDF(timestampColumn1, timestampColumn2, 'DAY TO HOUR', 'DAY')
from
tableName;

JSON_EXTRACT_DOT_NOTATION_UDF(JSON_OBJECT VARIANT, JSON_PATH STRING)

SELECT CAST(varcharColumn AS JSON(2000))..name FROM variantTest;

SELECT
JSON_EXTRACT_DOT_NOTATION_UDF(CAST(varcharColumn AS VARIANT), '$..name')
FROM
variantTest;

PUBLIC.WEEK_OF_MONTH_UDF(INPUT TIMESTAMP_TZ)

SELECT PUBLIC.WEEK_OF_MONTH_UDF(DATE '2021-10-26');

'4'

PUBLIC.DAYNAME_LONG_UDF(INPUT TIMESTAMP_TZ)

SELECT PUBLIC.DAYNAME_LONG_UDF(DATE '2022-06-30');

'Thursday'

PUBLIC.INTERVAL_TO_MONTHS_UDF
(INPUT_VALUE VARCHAR())

GETQUERYBANDVALUE_UDF(QUERYBAND VARCHAR, SEARCHTYPE FLOAT, SEARCHNAME VARCHAR)

SELECT GETQUERYBANDVALUE_UDF('=T> account=Matt;user=Matt200; =S> account=SaraDB;user=Sara;role=DbAdmin;', 0, 'account');
SELECT GETQUERYBANDVALUE_UDF('=T> account=Matt;user=Matt200; =S> account=SaraDB;user=Sara;role=DbAdmin;', 2, 'account');
SELECT GETQUERYBANDVALUE_UDF('=T> account=Matt;user=Matt200; =S> account=SaraDB;user=Sara;role=DbAdmin;', 0, 'role');
SELECT GETQUERYBANDVALUE_UDF('=T> account=Matt;user=Matt200; =S> account=SaraDB;user=Sara;role=DbAdmin;', 1, 'role');

      Matt
      SaraDB
      DbAdmin
      NULL

SELECT GETQUERYBANDVALUE('=T> account=Matt;user=Matt200; =S> account=SaraDB;user=Sara;role=DbAdmin;', 0, 'account')

WITH
--** MSC-WARNING - MSCEWI2078 - THE EXPAND ON CLAUSE FUNCTIONALITY IS TRANSFORMED INTO A CTE BLOCK **
ExpandOnCTE AS
(
SELECT
PUBLIC.EXPAND_ON_UDF('ANCHOR_SECOND', VALUE, duration) bg
FROM
project,
TABLE(FLATTEN(PUBLIC.ROW_COUNT_UDF(PUBLIC.DIFF_TIME_PERIOD_UDF('ANCHOR_SECOND', duration))))
)
SELECT NORMALIZE emp_id,
duration
FROM
project,
ExpandOnCTE;

PUBLIC.JULIAN_DAY_UDF(INPUT TIMESTAMP_TZ)

SELECT PUBLIC.JULIAN_DAY_UDF(DATE '2021-10-26');

'2459514'

PUBLIC.WEEKNUMBER_OF_MONTH_UDF(INPUT TIMESTAMP_TZ)

SELECT PUBLIC.WEEKNUMBER_OF_MONTH_UDF(DATE '2022-05-21')

3

JSON_EXTRACT_UDF(JSON_OBJECT VARIANT, JSON_PATH STRING, SINGLE_VALUE BOOLEAN)

SELECT
    Store.JSONExtract('$..author') as AllAuthors
FROM BookStores;

SELECT
    JSON_EXTRACT_UDF(Store, '$..author', FALSE) as AllAuthors
    FROM
    BookStores;

PUBLIC.COMPUTE_EXPAND_ON_UDF(TIME STRING, SEQ NUMBER, PERIOD TIMESTAMP, PERIODTYPE STRING)

PUBLIC.WEEK_NUMBER_OF_QUARTER_UDF(INPUT TIMESTAMP_TZ)

SELECT WEEK_NUMBER_OF_QUARTER_UDF(DATE '2023-01-01'),
WEEK_NUMBER_OF_QUARTER_UDF(DATE '2022-10-27')

1, 4

PUBLIC.YEAR_END_ISO_UDF(INPUT date)

SELECT  PUBLIC.YEAR_END_ISO_UDF(DATE '2022-01-01'),
PUBLIC.YEAR_END_ISO_UDF(DATE '2022-04-12');

2022-01-02, 2023-01-01

PUBLIC.INSERT_CURRENCY_UDF(INPUT VARCHAR, CURRENCYINDEX INTEGER, CURRENCYVALUE VARCHAR)

SELECT PUBLIC.INSERT_CURRENCY_UDF(to_char(823, 'S999999'), '1', 'CRC');

'+CRC823'