SUBSTR Function

Translation reference for DBMS_RANDOM.SUBSTR.

Description

This function returns amount bytes or characters of a LOB, starting from an absolute offset 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

DBMS_LOB.SUBSTR (
   lob_loc     IN    BLOB,
   amount      IN    INTEGER := 32767,
   offset      IN    INTEGER := 1)
  RETURN RAW;

DBMS_LOB.SUBSTR (
   lob_loc     IN    CLOB   CHARACTER SET ANY_CS,
   amount      IN    INTEGER := 32767,
   offset      IN    INTEGER := 1)
  RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;

DBMS_LOB.SUBSTR (
   file_loc     IN    BFILE,
   amount      IN    INTEGER := 32767,
   offset      IN    INTEGER := 1)
  RETURN RAW;

Function overloads

DBMS_LOB.SUBSTR('string', amount, offset)

Usage example

Oracle 

SELECT 
-- 1. "some magic here"
DBMS_LOB.SUBSTR('some magic here', 15, 1) "1",
-- 2. "some"
DBMS_LOB.SUBSTR('some magic here', 4, 1) "2",
-- 3. "me magic here"
DBMS_LOB.SUBSTR('some magic here', 15, 3) "3",
-- 4. "magic"
DBMS_LOB.SUBSTR('some magic here', 5, 6) "4",
-- 5. "here"
DBMS_LOB.SUBSTR('some magic here', 20, 12) "5",
-- 6. " "
DBMS_LOB.SUBSTR('some magic here', 250, 16) "6"
FROM DUAL;

Snowflake 

SELECT 
-- 1. "some magic here"
SUBSTR('some magic here', 1, 15) "1",
-- 2. "some"
SUBSTR('some magic here', 1, 4) "2",
-- 3. "me magic here"
SUBSTR('some magic here', 3, 15) "3",
-- 4. "magic"
SUBSTR('some magic here', 6, 5) "4",
-- 5. "here"
SUBSTR('some magic here', 12, 20) "5",
-- 6. " "
SUBSTR('some magic here', 16, 250) "6";

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 

-- Create Table
CREATE TABLE blobtable( blob_column BLOB );

-- Insert sample value
INSERT INTO blobtable VALUES (utl_raw.cast_to_raw('some magic here'));

-- Select different examples
SELECT 
-- 1. "some magic here"
DBMS_LOB.SUBSTR(blob_column, 15, 1) "1",
-- 2. "some"
DBMS_LOB.SUBSTR(blob_column, 4, 1) "2",
-- 3. "me magic here"
DBMS_LOB.SUBSTR(blob_column, 15, 3) "3",
-- 4. "magic"
DBMS_LOB.SUBSTR(blob_column, 5, 6) "4",
-- 5. "here"
DBMS_LOB.SUBSTR(blob_column, 20, 12) "5",
-- 6. " "
DBMS_LOB.SUBSTR(blob_column, 250, 16) "6"
FROM BLOBTABLE;

Snowflake 

-- Create Table
CREATE OR REPLACE TABLE PUBLIC.blobtable (
blob_column BINARY /*** MSC-WARNING - MSCEWI1036 - BLOB DATA TYPE CONVERTED TO BINARY ***/
);

-- Insert sample value
INSERT INTO PUBLIC.blobtable
VALUES (TO_BINARY('some magic here', 'utf-8') );

-- Select different examples
SELECT
-- 1. "some magic here"
SUBSTR(blob_column, 1, 15) "1",
-- 2. "some"
SUBSTR(blob_column, 1, 4) "2",
-- 3. "me magic here"
SUBSTR(blob_column, 3, 15) "3",
-- 4. "magic"
SUBSTR(blob_column, 6, 5) "4",
-- 5. "here"
SUBSTR(blob_column, 12, 20) "5",
-- 6. " "
SUBSTR(blob_column, 16, 250) "6"
FROM
PUBLIC.BLOBTABLE;

Note: UTL_RAW.CAST_TO_RAW() is currently not being transformed to TO_BINARY(). The function is used to show the functional equivalence of the example.

DBMS_LOB.SUBSTR(CLOB, amount, offset)

Usage example

Oracle 

-- Create Table
CREATE TABLE clobtable(clob_column CLOB);

-- Insert sample value
INSERT INTO clobtable VALUES ('some magic here');

-- Select
SELECT 
-- 1. "some magic here"
DBMS_LOB.SUBSTR(clob_column, 15, 1) "1",
-- 2. "some"
DBMS_LOB.SUBSTR(clob_column, 4, 1) "2",
-- 3. "me magic here"
DBMS_LOB.SUBSTR(clob_column, 15, 3) "3",
-- 4. "magic"
DBMS_LOB.SUBSTR(clob_column, 5, 6) "4",
-- 5. "here"
DBMS_LOB.SUBSTR(clob_column, 20, 12) "5",
-- 6. " "
DBMS_LOB.SUBSTR(clob_column, 250, 16) "6"
FROM clobtable;

Snowflake 

-- Create Table
CREATE OR REPLACE TABLE PUBLIC.clobtable (
clob_column VARCHAR /*** MSC-WARNING - MSCEWI1036 - CLOB DATA TYPE CONVERTED TO VARCHAR ***/
);

-- Insert sample value
INSERT INTO PUBLIC.clobtable
VALUES ('some magic here');

-- Select
SELECT
-- 1. "some magic here"
SUBSTR(clob_column, 15, 1) "1",
-- 2. "some"
SUBSTR(clob_column, 4, 1) "2",
-- 3. "me magic here"
SUBSTR(clob_column, 15, 3) "3",
-- 4. "magic"
SUBSTR(clob_column, 5, 6) "4",
-- 5. "here"
SUBSTR(clob_column, 20, 12) "5",
-- 6. " "
SUBSTR(clob_column, 250, 16) "6"
FROM
PUBLIC.clobtable;

Note: UTL_RAW.CAST_TO_RAW() is currently not being transformed to TO_BINARY(). The function is used to show the functional equivalence of the example.

DBMS_LOB.SUBSTR(BFILE, amount, offset)

Usage example

Using DBMS_LOB.SUBSTR() on a BFILE column returns a substring of the file content.

Next example is not a current migration, but a functional example to show the differences of the SUBSTR function on BFILE types.

File Content (file.txt):

some magic here

Oracle 

CREATE OR REPLACE PROCEDURE bfile_substr_procedure
IS
    fil BFILE := BFILENAME('MY_DIR', 'file.txt');
BEGIN
    DBMS_LOB.FILEOPEN(fil, DBMS_LOB.FILE_READONLY);
    DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(fil,9,1)));
    --Console Output:
    -- "some magi"
    DBMS_LOB.FILECLOSE(fil);
END;

Snowflake

BFILE columns are translated into VARCHAR columns, therefore applying a SUBSTR function on the same column would return a substring of the file name, not the file content.

-- Create Bfile Table
CREATE OR REPLACE TABLE PUBLIC.bfile_table (
bfile_column VARCHAR /*** MSC-WARNING - MSCEWI1036 - BFILE DATA TYPE CONVERTED TO VARCHAR ***/
/*** MSC-WARNING - MSCEWI3105 - ADDITIONAL WORK IS NEEDED FOR BFILE COLUMN USAGE. BUILD_STAGE_FILE_URL FUNCTION IS A RECOMMENDED WORKAROUND ***/
);

-- Example Insert
INSERT INTO PUBLIC.bfile_table
VALUES (
/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'BFILENAME_UDF' INSERTED. ***/
PUBLIC.BFILENAME_UDF('MY_DIR', 'file.txt'));

-- SUBSTR on Bfile column
SELECT
/*** MSC-ERROR - MSCEWI3121 - USING DBMS_LOB.SUBSTR ON BFILE SOURCE COLUMN IS NOT SUPPORTED ON SNOWFLAKE ***/
SUBSTR(bfile_column, 1, 9) FROM PUBLIC.bfile_table;

Known Issues

1. Using DBMS_LOB.SUBSTR with BFILE columns

The current transformation for BFILE datatypes in columns is VARCHAR, where the name of the file is stored as a string. Therefore applying the SUBSTR function on a BFILE column after transformation will return a substring of the file name, while Oracle would return a substring of the file content.

Related EWIs

  1. MSCEWI3105: Additional Work Is Needed For BFILE Column Usage

  2. MSCEWI3121: Using DBMS_LOB.SUBSTR built-in package with a BFILE column is not supported in Snowflake.

PreviousDBMS_LOBNextDBMS_RANDOM

Last updated