Text literals
Description
Use the text literal notation to specify values whenever
string
appears in the syntax of expressions, conditions, SQL functions, and SQL statements in other parts of this reference.
[ {N | n} ]
{ '[ c ]...'
| { Q | q } 'quote_delimiter c [ c ]... quote_delimiter'
}
Sample Source Patterns
Empty string ('')
The empty strings are equivalent to NULL in Oracle, so in order to emulate the behavior in Snowflake, the empty strings are converted to NULL or undefined depending if the literal is used inside a procedure or not.
Oracle
SELECT UPPER('') FROM DUAL;
Snowflake
SELECT UPPER(NULL) FROM DUAL;
Empty string in stored procedures
Oracle
CREATE TABLE empty_string_table(
col1 VARCHAR(10),
col2 VARCHAR(10));
CREATE OR REPLACE PROCEDURE null_proc AS
var1 INTEGER := '';
var3 INTEGER := null;
var2 VARCHAR(20) := 'hello';
BEGIN
var1 := var1 + 456;
var2 := var2 || var1;
IF var1 IS NULL THEN
INSERT INTO empty_string_table VALUES (var1, var2);
END IF;
END;
CALL null_proc();
SELECT * FROM empty_string_table;
Snowflake
CREATE OR REPLACE TABLE PUBLIC.empty_string_table (
col1 VARCHAR(10),
col2 VARCHAR(10));
CREATE OR REPLACE PROCEDURE PUBLIC.null_proc ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
let VAR1 = undefined;
let VAR3 = undefined;
let VAR2 = `hello`;
VAR1 = VAR1 + 456;
// ** MSC-WARNING - MSCEWI1038 - THIS STATEMENT MAY BE A DYNAMIC SQL THAT COULD NOT BE RECOGNIZED AND CONVERTED **
VAR2 = `${concatValue(VAR2)}${concatValue(VAR1)}`;
if (IS_NULL(VAR1)) {
EXEC(`INSERT INTO PUBLIC.empty_string_table VALUES (?, ?)`,[VAR1,VAR2]);
}
$$;
CALL PUBLIC.null_proc();
SELECT * FROM PUBLIC.empty_string_table;
Empty string in built-in functions
The transformation does not apply when the empty string is used as an argument of the REPLACE and CONCAT functions in order to keep the functional equivalence.
Oracle
SELECT REPLACE('Hello world', '', 'l'), CONCAT('A','') FROM DUAL;
Snowflake
SELECT REPLACE('Hello world', '', 'l'), CONCAT('A','') FROM DUAL;
Known Issues
No issues were found.
Related EWIs
MSCEWI1038: Dynamic SQL statement may be unrecognized.
Last updated
Was this helpful?