This is a deprecated version of the SnowConvert documentation, please visit the official site .
Severity
Medium
Description
Snowflake does not support negative values for the function, then this will cause different behavior when executed.
Example Code
Input Code:
CREATE TABLE TABLE1
(
COL0 NUMBER DEFAULT INSTR('CORPORATE FLOOR','OR', -3, 2),
COL1 NUMBER DEFAULT INSTRB('CORPORATE FLOOR','OR', 3),
COL2 NUMBER DEFAULT INSTRC('CORPORATE FLOOR','OR'),
COL3 NUMBER DEFAULT INSTR2('CORPORATE FLOOR','OR', 3, 2),
COL4 NUMBER DEFAULT INSTR4('CORPORATE FLOOR','OR', 3, 2)
);
Output Code:
CREATE OR REPLACE TABLE MYDB.PUBLIC.TABLE1 (
COL0 NUMBER (38,19) DEFAULT REGEXP_INSTR('CORPORATE FLOOR','OR', -3, 2) /*** MSC-WARNING - MSCEWI3079 - INSTR FUNCTION WAS CONVERTED TO REGEXP_INSTR. THE SECOND ARGUMENT MAY REQUIRE CHANGES ***/ /*** MSC-ERROR - MSCEWI3020 - NEGATIVE VALUES NOT SUPPORTED FOR FUNCTION ***/,
COL1 NUMBER (38,19) DEFAULT POSITION('OR', 'CORPORATE FLOOR', 3),
COL2 NUMBER (38,19) DEFAULT POSITION('OR', 'CORPORATE FLOOR'),
COL3 NUMBER (38,19) DEFAULT REGEXP_INSTR('CORPORATE FLOOR','OR', 3, 2) /*** MSC-WARNING - MSCEWI3079 - INSTR FUNCTION WAS CONVERTED TO REGEXP_INSTR. THE SECOND ARGUMENT MAY REQUIRE CHANGES ***/,
COL4 NUMBER (38,19) DEFAULT REGEXP_INSTR('CORPORATE FLOOR','OR', 3, 2) /*** MSC-WARNING - MSCEWI3079 - INSTR FUNCTION WAS CONVERTED TO REGEXP_INSTR. THE SECOND ARGUMENT MAY REQUIRE CHANGES ***/);
Recommendations
Create a User Defined Function that can handle the negative parameter or look for another alternative.
