SnowConvert: SQL Serverの関数参照¶

ISNUMERIC_UDF(EXPR VARCHAR)

SELECT ISNUMERIC_UDF('5');

1

PATINDEX_UDF(PATTERN VARCHAR, EXPRESSION VARCHAR)

SELECT PATINDEX_UDF('an', 'banana');

2

ERROR_SEVERITY_UDF()

SELECT ERROR_SEVERITY_UDF();

null -- No information set.

TRANSFORM_SP_EXECUTE_SQL_STRING_UDF(
    _SQL_STRING STRING,
    _PARAMS_DEFINITION STRING,
    _PARAMS_NAMES ARRAY,
    _PARAMS_VALUES ARRAY
)

SELECT TRANSFORM_SP_EXECUTE_SQL_STRING_UDF(
    'SELECT * FROM PERSONS WHERE NAME LIKE (@NAME) AND ID < @id AND AGE < @age;', '@age INT, @id INT, @name VARCHAR(25)',
    ARRAY_CONSTRUCT('', '', ''),
    ARRAY_CONSTRUCT(30, 100, 'John Smith'));

SELECT * FROM PERSONS WHERE NAME LIKE ('John Smith') AND ID < 100 AND AGE < 30;

TABLE_OBJECT_ID_UDF(NAME VARCHAR)

SELECT TABLE_OBJECT_ID_UDF('Test');

FALSE

ERROR_PROCEDURE_UDF()

SELECT ERROR_PROCEDURE_UDF();

null -- No information set.

DB_ID_UDF(p_database_name STRING)

SELECT DB_ID_UDF('MY_DATABASE')

6

ERROR_LINE_UDF()

SELECT ERROR_LINE_UDF();

null -- No information set.

VIEW_OBJECT_ID_UDF(NAME VARCHAR)

SELECT FUNCTION_OBJECT_ID_UDF('Test');

FALSE

CONSTRAINT_OBJECT_ID_UDF(NAME VARCHAR)

SELECT CONSTRAINT_OBJECT_ID_UDF('Test');

FALSE

FOR_XML_UDF(OBJ OBJECT, ELEMENT_NAME VARCHAR, ROOT_NAME VARCHAR)

SELECT
FOR_XML_UDF(OBJECT_CONSTRUCT('id', 1, 'name', 'David'), 'employee', 'employees');

OBJECT_ID_UDF(NAME VARCHAR)

SELECT OBJECT_ID_UDF('Test');

FALSE

PROCEDURE_OBJECT_ID_UDF(NAME VARCHAR)

SELECT PROCEDURE_OBJECT_ID_UDF('Test');

FALSE

ISDATE_UDF(DATE_VALUE STRING)

SELECT ISDATE_UDF('2024-01-26');

1

ERROR_NUMBER_UDF()

SELECT ERROR_NUMBER_UDF();

null -- No information set.

OFFSET_FORMATTER(offset_hrs VARCHAR)

 SELECT OFFSET_FORMATTER('2024-01-26 22:00:50.708 -0800');

2024-01-26 22:00:50.708 -0800

OPENXML_UDF(XML VARCHAR, PATH VARCHAR)

SELECT * FROM TABLE(OPENXML_UDF('<iceCreamOrders>
    <order>
        <customer customerID="CUST001" contactName="Test ABC">
            <iceCreamOrder orderID="ORD001" employeeID="101" orderDate="2023-05-15T14:30:00">
                <iceCreamDetail productID="001" quantity="2"/>
                <iceCreamDetail productID="003" quantity="1"/>
            </iceCreamOrder>
        </customer>
    </order>
    <order>
        <customer customerID="CUST002" contactName="Test XYZ">
            <iceCreamOrder orderID="ORD002" employeeID="102" orderDate="2023-06-20T12:45:00">
                <iceCreamDetail productID="005" quantity="3"/>
                <iceCreamDetail productID="007" quantity="2"/>
            </iceCreamOrder>
        </customer>
    </order>
</iceCreamOrders>
', 'iceCreamOrders:order'));

QUOTENAME_UDF(STR VARCHAR, QUOTECHAR VARCHAR)

SELECT QUOTENAME_UDF('test', '?');

?test?

UPDATE_ERROR_VARS_UDF(MESSAGE STRING, SEVERITY STRING, STATE STRING)

  SELECT UPDATE_ERROR_VARS_UDF('Message', '1', '1');

1ABC1

ROUND_MILLISECONDS_UDF(INPUT TIMESTAMP_TZ)

SELECT PUBLIC.ROUND_MILLISECONDS_UDF('1900-01-01 00:00:00.995 +0100')

'1900-01-01 00:00:00.997 +0100'

CAST_NUMERIC_TO_TIMESTAMP_TZ_UDF(INPUT NUMBER)

SELECT PUBLIC.CAST_NUMERIC_TO_TIMESTAMP_TZ_UDF(0)

1900-01-01 01:00:00.000 +0100

IDENTITY_UDF()

IDENTITY_UDF()

1

FOR_XML_UDF(OBJ OBJECT, ELEMENT_NAME VARCHAR)

SELECT
FOR_XML_UDF(OBJECT_CONSTRUCT('id', 1, 'name', 'David'), 'employee');

QUOTENAME_UDF(STR VARCHAR)

SELECT QUOTENAME_UDF('test');

"test"

VIEW_OBJECT_ID_UDF(NAME VARCHAR)

SELECT VIEW_OBJECT_ID_UDF('Test');

FALSE

PUBLIC.SUBTRACT_TIMESTAMP_TZ_UDF(FIRST_DATE TIMESTAMP_TZ, SECOND_DATE TIMESTAMP_TZ)

SELECT SUBTRACT_TIMESTAMP_TZ_UDF('1900-01-01 00:00:00.000 +0100', '1900-01-01 00:00:00.003 -0100')

1899-12-31 13:59:59.997 -0800

STR_UDF(FLOAT_EXPR FLOAT, FORMAT VARCHAR)

SELECT STR_UDF(1.5, '99');

2

XML_JSON_SIMPLE(XML VARIANT)

SELECT XML_JSON_SIMPLE(TO_VARIANT(PARSE_XML('<iceCreamOrders>
    <order>
        <customer customerID="CUST001" contactName="Test ABC">
            <iceCreamOrder orderID="ORD001" employeeID="101" orderDate="2023-05-15T14:30:00">
                <iceCreamDetail productID="001" quantity="2"/>
                <iceCreamDetail productID="003" quantity="1"/>
            </iceCreamOrder>
        </customer>
    </order>
    <order>
        <customer customerID="CUST002" contactName="Test XYZ">
            <iceCreamOrder orderID="ORD002" employeeID="102" orderDate="2023-06-20T12:45:00">
                <iceCreamDetail productID="005" quantity="3"/>
                <iceCreamDetail productID="007" quantity="2"/>
            </iceCreamOrder>
        </customer>
    </order>
</iceCreamOrders>
')));

{
  "iceCreamOrders": {
    "$name": "iceCreamOrders",
    "order": [
      {
        "order": {
          "$name": "order",
          "customer": [
            {
              "customer": {
                "$name": "customer",
                "@contactName": "Test ABC",
                "@customerID": "CUST001",
                "iceCreamOrder": [
                  {
                    "iceCreamOrder": {
                      "$name": "iceCreamOrder",
                      "@employeeID": 101,
                      "@orderDate": "2023-05-15T14:30:00",
                      "@orderID": "ORD001",
                      "iceCreamDetail": [
                        {
                          "iceCreamDetail": {
                            "$name": "iceCreamDetail",
                            "@productID": "001",
                            "@quantity": 2
                          }
                        },
                        {
                          "iceCreamDetail": {
                            "$name": "iceCreamDetail",
                            "@productID": "003",
                            "@quantity": 1
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      },
      {
        "order": {
          "$name": "order",
          "customer": [
            {
              "customer": {
                "$name": "customer",
                "@contactName": "Test XYZ",
                "@customerID": "CUST002",
                "iceCreamOrder": [
                  {
                    "iceCreamOrder": {
                      "$name": "iceCreamOrder",
                      "@employeeID": 102,
                      "@orderDate": "2023-06-20T12:45:00",
                      "@orderID": "ORD002",
                      "iceCreamDetail": [
                        {
                          "iceCreamDetail": {
                            "$name": "iceCreamDetail",
                            "@productID": "005",
                            "@quantity": 3
                          }
                        },
                        {
                          "iceCreamDetail": {
                            "$name": "iceCreamDetail",
                            "@productID": "007",
                            "@quantity": 2
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    ]
  }
}

FORMATMESSAGE_UDF(MESSAGE STRING, ARGS ARRAY)

SELECT FORMATMESSAGE_UDF('Test %s!', TO_ARRAY('a'));

Test a!

IS_MEMBER_UDF(ROLE STRING)

SELECT IS_MEMBER_UDF('TEST');

FALSE

RAISERROR_UDF(MSG_ID DOUBLE, SEVERITY DOUBLE, STATE DOUBLE, PARAMS ARRAY)

SELECT RAISERROR_UDF(2.1, 1.6, 1.0, array_construct('More information'));

MESSAGE: 2.1, LEVEL: 1.6, STATE: 1

STR_UDF(FLOAT_EXPR FLOAT, FORMAT VARCHAR)

SELECT STR_UDF(1.5);

2

SWITCHOFFSET_UDF(source_timestamp TIMESTAMP_TZ, target_tz varchar)

SELECT SWITCHOFFSET_UDF(time_in_paris, '-0600') as time_in_costa_rica;

GET_CURRENT_TIMEZONE_UDF()

SELECT PUBLIC.GET_CURRENT_TIMEZONE_UDF();

'Europe/London'

UPDATE_ERROR_VARS_UDF(LINE STRING,CODE STRING, STATE STRING, MESSAGE STRING, PROC_NAME STRING, SEVERITY STRING)

  SELECT UPDATE_ERROR_VARS_UDF('1', '1', '1', 'ABC', 'TEST', '1');

111ABCTEST1

SEQUENCE_OBJECT_ID_UDF(NAME VARCHAR)

SELECT SEQUENCE_OBJECT_ID_UDF('Test');

FALSE

CAST_TIMESTAMP_TZ_TO_NUMERIC_UDF(INPUT TIMESTAMP_TZ)

SELECT PUBLIC.CAST_TIMESTAMP_TZ_TO_NUMERIC_UDF('1900-01-01 01:00:00.000 +0100')

0

RAISERROR_UDF(MSG_TEXT VARCHAR, SEVERITY DOUBLE, STATE DOUBLE, PARAMS ARRAY)

SELECT RAISERROR_UDF('<\<%*.*s>> TEST', 1.0, 1, array_construct());

MESSAGE: <<undefined>> TEST, LEVEL: 1, STATE: 1

PARSENAME_UDF(STR VARCHAR, PART_NUMBER INT)

SELECT PARSENAME_UDF('Test_A.Test_B.Test_C]', 2);

Test_B

ERROR_STATE_UDF()

SELECT ERROR_STATE_UDF();

null -- No information set.

CAST_TIME_TO_TIMESTAMP_TZ_UDF(INPUT TIME)

SELECT PUBLIC.CAST_TIME_TO_TIMESTAMP_TZ_UDF('00:00:00.995')

1900-01-01 00:00:00.997

SUM_TIMESTAMP_TZ_UDF(FIRST_DATE TIMESTAMP_TZ, SECOND_DATE TIMESTAMP_TZ)

SELECT SUM_TIMESTAMP_TZ_UDF('1900-01-01 00:00:00.000 +0100', '1900-01-01 00:00:00.003 -0100')

1900-01-01 00:00:00.003 +0000