ISNULL ( check_expression , replacement_value )  

NVL( <expr1> , <expr2> )

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

NEWID ( )  

UUID_STRING()

SELECT
UUID_STRING( ) AS ID;

NULLIF ( check_expression , replacement_value )  

NULLIF( <expr1> , <expr2> )

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

@@ROWCOUNT

SQLROWCOUNT

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();

CHAR( expression )

{CHR | CHAR} ( <input> )

String.fromCharCode( expression1, ... , expressionN )

SELECT
CHAR(170) AS SMALLEST_A;

CREATE OR REPLACE FUNCTION get_char(expression float)
RETURNS string
LANGUAGE JAVASCRIPT
AS
$$
  return String.fromCharCode( EXPRESSION );
$$;

SELECT GET_CHAR(170) SMALLEST_A;

CHARINDEX( expression_to_find, expression_to_search [, start] )

CHARINDEX( <expr1>, <expr2> [ , <start_pos> ] )

String.indexOf( search_value [, index] )

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;

COALESCE ( expression [ ,...n ] )   

COALESCE( <expr1> , <expr2> [ , ... , <exprN> ] )

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

CONCAT ( string_value1, string_value2 [, string_valueN ] )  

CONCAT( <expr1> [ , <exprN> ... ] )

<expr1> || <expr2> [ || <exprN> ... ]

 String.concat( expression1, ..., expressionN )

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

CREATE OR REPLACE FUNCTION concatenate_strs(strings array)
RETURNS string
LANGUAGE JAVASCRIPT
AS
$$
  var result = ""
  STRINGS.forEach(element => result = result.concat(element));
  return result;
$$;
SELECT concatenate_strs(array_construct('Ray',' ','of',' ','Light')) TITLE;

LEFT ( character_expression , integer_expression )  

LEFT ( <expr> , <length_expr> )

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('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;

LEN( string_expression )

LENGTH( <expression> )
LEN( <expression> )

 string.length

SELECT LEN('Sample text') AS LEN;

CREATE OR REPLACE FUNCTION get_len(str varchar)
RETURNS float
LANGUAGE JAVASCRIPT
AS
$$
  return STR.length;
$$;
SELECT GET_LEN('Sample text') LEN;

LOWER ( character_expression )  

LOWER( <expr> )

 String.toLowerCase( )

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

CREATE OR REPLACE FUNCTION to_lower(str varchar)
RETURNS string
LANGUAGE JAVASCRIPT
AS
$$
  return STR.toLowerCase();
$$;

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

REPLACE ( string_expression , string_pattern , string_replacement )  

REPLACE( <subject> , <pattern> [ , <replacement> ] )

 String.replace( pattern, new_expression)

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

COLUMNNAME           |
---------------------|
Real computer science|

 CREATE OR REPLACE FUNCTION REPLACER (str varchar, pattern varchar, new_expression varchar)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
   return STR.replace( PATTERN, NEW_EXPRESSION );
$$;

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

COLUMNNAME             |
---------------------|
Real computer science|

REPLICATE( string_expression, number_expression )

REPEAT(<input>, <n>)

String.repeat( number_expression )

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

RESULT                                                           |
-----------------------------------------------------------------|
Staying aliveStaying aliveStaying aliveStaying aliveStaying alive|

 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;

RESULT                                                           |
-----------------------------------------------------------------|
Staying aliveStaying aliveStaying aliveStaying aliveStaying alive|

RIGHT ( character_expression , integer_expression )  

RIGHT( <expr> , <length_expr> )

 function RIGHT(string, index){
      if(index< 0){
          throw new RangeError('Invalid INDEX on RIGHT function');
      }
      return string.slice( string.length - index, string.length );
    }

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

   LAST_NAME|      
------------|
       Smith|   

 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;

   LAST_NAME|      
------------|
       Smith|   

RTRIM( string_expression )

RTRIM(<expr> [, <characters> ])

 function RTRIM(string){
    return string.replace(/s+$/,"");
}

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

RTRIM                |
---------------------|
LAST TWO BLANK SPACES|

 CREATE OR REPLACE FUNCTION rtrim(str varchar)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  function RTRIM(string){
    return string.replace(/s+$/,"");
    }
   return RTRIM( STR );
$$;

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

RTRIM                |
---------------------|
LAST TWO BLANK SPACES|

SPACE ( integer_expression )  

SPACE(<n>)

 function SPACE( occurrences ){
    return ' '.repeat( occurrences );
}

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

RESULT       |
-------------|
SOME     TEXT|

 CREATE OR REPLACE FUNCTION SPACE(occurrences float)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
    function SPACE( occurrences ){
    return ' '.repeat( occurrences );
    }
    return SPACE( OCCURRENCES );
$$;

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

RESULT       |
-------------|
SOME     TEXT|

SUBSTRING( string_expression, start, length )

SUBSTR( <base_expr>, <start_expr> [ , <length_expr> ] )

SUBSTRING( <base_expr>, <start_expr> [ , <length_expr> ] )

 string.substring( indexA [, indexB])

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

SOMETEXT|
--------|
bcd     |

 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;

SOMETEXT|
--------|
bcd     |

UPPER( string_expression ) 

UPPER( <expr> )

 String.toUpperCase( )

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