SUBSTR Function
Translation reference for DBMS_RANDOM.SUBSTR.
Description
This function returns
amount
bytes or characters of a LOB, starting from an absoluteoffset
from the beginning of the LOB. (Oracle PL/SQL DBMS_LOB.SUBSTR)
This built-in function is replaced with Snowflake SUBSTR function. However, there are some differences.
Note: The amount and offset parameters are inverted in Snowflake
Syntax
Function overloads
DBMS_LOB.SUBSTR('string', amount, offset)
Usage example
Oracle
IN -> Oracle_01.sql
Snowflake
OUT -> Oracle_01.sql
DBMS_LOB.SUBSTR(BLOB, amount, offset)
Usage example
Note: Result values in Oracle and Snowflake are being converted from bytes to strings for easier understanding of the function.
For Snowflake consider using:
hex_decode_string( to_varchar(SUBSTR(blob_column, 1, 6), 'HEX'));
and for Oracle consider using:
utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(blob_column, 1, 6));
to obtain the result as a string.
Oracle
IN -> Oracle_02.sql
Snowflake
OUT -> Oracle_02.sql