SnowConvert: Transact Built-in functions¶

 SELECT ISNULL(NULL, 'SNOWFLAKE') AS COMPANYNAME;

 SELECT
NVL(NULL, 'SNOWFLAKE') AS COMPANYNAME;

 SELECT NEWID ( ) AS ID;

 SELECT
UUID_STRING( ) AS ID;

 SELECT NULLIF(6,9) AS RESULT1, NULLIF(5,5) AS RESULT2;

 SELECT
NULLIF(6,9) AS RESULT1,
NULLIF(5,5) AS RESULT2;

 CREATE TABLE table1
(
    column1 INT
);

CREATE PROCEDURE procedure1
AS
BEGIN
    declare @addCount int = 0;

    INSERT INTO table1 (column1) VALUES (1),(2),(3);
    set @addCount = @addCount + @@ROWCOUNT

    select @addCount
END
;
GO

EXEC procedure1;

 CREATE OR REPLACE TABLE table1
(
    column1 INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "11/13/2024",  "domain": "test" }}'
;

CREATE OR REPLACE PROCEDURE procedure1 ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "11/13/2024",  "domain": "test" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        ADDCOUNT INT := 0;
        ProcedureResultSet RESULTSET;
    BEGIN
         

        INSERT INTO table1 (column1) VALUES (1),(2),(3);
        ADDCOUNT := :ADDCOUNT + SQLROWCOUNT;
        ProcedureResultSet := (

        select
            :ADDCOUNT);
        RETURN TABLE(ProcedureResultSet);
    END;
$$;

CALL procedure1();

 SELECT FORMATMESSAGE('This is the %s and this is the %s.', 'first variable', 'second variable') AS RESULT;

 SELECT
--** SSC-FDM-TS0008 - FORMATMESSAGE WAS CONVERTED TO CUSTOM UDF FORMATMESSAGE_UDF AND IT MIGHT HAVE A DIFFERENT BEHAVIOR. **
FORMATMESSAGE_UDF('This is the %s and this is the %s.', ARRAY_CONSTRUCT('first variable', 'second variable')) AS RESULT;

 SELECT CHAR(170) AS SMALLEST_A

 SELECT
CHAR(170) AS SMALLEST_A;

 SELECT CHARINDEX('t', 'Customer') AS MatchPosition;

 SELECT
CHARINDEX('t', 'Customer') AS MatchPosition;

 CREATE OR REPLACE FUNCTION get_index
(
  expression_to_find varchar, 
  expression_to_search varchar, 
  start_index  float
)
  RETURNS float
  LANGUAGE JAVASCRIPT
AS
$$
  return EXPRESSION_TO_SEARCH.indexOf(EXPRESSION_TO_FIND, START_INDEX)+1;
$$;

SELECT GET_INDEX('and', 'Give your heart and soul to me, and life will always be la vie en rose', 20) AS INDEX;

 SELECT TOP 10 StartDate, 
COALESCE(EndDate,'2000-01-01') AS FIRST_NOT_NULL 
FROM HumanResources.EmployeeDepartmentHistory

 SELECT TOP 10
StartDate,
COALESCE(EndDate,'2000-01-01') AS FIRST_NOT_NULL
FROM
HumanResources.EmployeeDepartmentHistory;

 SELECT CONCAT('Ray',' ','of',' ','Light') AS TITLE;

 SELECT
CONCAT('Ray',' ','of',' ','Light') AS TITLE;

 SELECT LEFT('John Smith', 5) AS FIRST_NAME;

 SELECT LEFT('John Smith', 5) AS FIRST_NAME;

 CREATE OR REPLACE FUNCTION left_str(str varchar, index float)
RETURNS string
LANGUAGE JAVASCRIPT
AS
$$
    function LEFT(string, index){
      if(index < 0){
          throw new RangeError('Invalid INDEX on LEFT function');
      }
      return string.slice( 0, index);
    }
  return LEFT(STR, INDEX);
$$;
SELECT LEFT_STR('John Smith', 5) AS FIRST_NAME;

 SELECT LEN('Sample text') AS [LEN];

 SELECT LEN('Sample text') AS LEN;

 SELECT LOWER('YOU ARE A PREDICTION OF THE GOOD ONES') AS LOWERCASE;

 SELECT LOWER('YOU ARE A PREDICTION OF THE GOOD ONES') AS LOWERCASE;

 SELECT NCHAR(170);

 SELECT REPLACE('Real computer software', 'software','science') AS COLUMNNAME;

 SELECT REPLACE('Real computer software', 'software','science') AS COLUMNNAME;

 SELECT REPLICATE('Staying alive',5) AS RESULT

 SELECT REPEAT('Staying alive',5) AS RESULT;

 CREATE OR REPLACE FUNCTION REPEAT_STR (str varchar, occurrences float)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
 
   return STR.repeat( OCCURRENCES );
$$;

SELECT REPEAT_STR('Staying alive ', 5) AS RESULT;

 SELECT RIGHT('John Smith', 5) AS LAST_NAME;

 SELECT RIGHT('John Smith', 5) AS LAST_NAME;

 CREATE OR REPLACE FUNCTION right_str(str varchar, index float)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
    function RIGHT(string, index){
      if(index< 0){
          throw new RangeError('Invalid INDEX on RIGHT function');
      }
      return string.slice( string.length - index, string.length );
    }
  return RIGHT(STR, INDEX);
$$;

SELECT RIGHT_STR('John Smith', 5) AS LAST_NAME;

 SELECT RTRIM('LAST TWO BLANK SPACES  ') AS [RTRIM]

 SELECT RTRIM('LAST TWO BLANK SPACES  ') AS RTRIM;

 SELECT CONCAT('SOME', SPACE(5), 'TEXT') AS RESULT;

 SELECT CONCAT('SOME', SPACE(5), 'TEXT') AS RESULT;

 SELECT SUBSTRING('abcdef', 2, 3) AS SOMETEXT;  

 SELECT SUBSTRING('abcdef', 2, 3) AS SOMETEXT;  

 CREATE OR REPLACE FUNCTION REPLACER_LENGTH(str varchar, index float, length float)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
    var start = INDEX - 1;
    var end = STR.length - (LENGTH - 1);
    return STR.substring(start, end);
$$;

SELECT REPLACER_LENGTH('abcdef', 2, 3) AS SOMETEXT;

 SELECT UPPER('you are a prediction of the good ones') AS [UPPER]

 SELECT
UPPER('you are a prediction of the good ones') AS UPPER;

 SELECT ASCII('A') AS A , ASCII('a') AS a;

 CREATE OR REPLACE FUNCTION get_ascii(c char)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  return C.charCodeAt();
$$;

SELECT GET_ASCII('A') A, GET_ASCII('a') a;

 SELECT QUOTENAME('Hello', '`') AS HELLO;

 CREATE OR REPLACE FUNCTION QUOTENAME(str varchar, quote char)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  function QUOTENAME(string, quote){
    const allowed_quotes = /[\']|[\"]|[(]|[)]|[\[]|[\]]|[\{]|[\}]|[\`]/;
    
    if(!allowed_quotes.test(quote)) throw new TypeError('Invalid Quote');
    
    return quote.concat(string, quote);
  }
   return QUOTENAME(STR, QUOTE);
$$;

SELECT QUOTENAME('Hola', '`') HELLO;

 SELECT CONCAT_WS(' ', 'Mariah','Carey') AS NAME;

 CREATE OR REPLACE FUNCTION join_strs(separator varchar, strings array)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  return STRINGS.join(SEPARATOR);
$$;
SELECT join_strs(' ',array_construct('Mariah','Carey')) NAME;

 SELECT SOUNDEX('two') AS TWO , SOUNDEX('too') AS TOO;