This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.
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.
