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

IN -> Oracle_01.sql

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;

1              |2   |3            |4    |5   |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |

Snowflake

OUT -> Oracle_01.sql

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"
FROM DUAL;

1              |2   |3            |4    |5   |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |

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

-- 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;

1              |2   |3            |4    |5   |6|
---------------+----+-------------+-----+----+-+
some magic here|some|me magic here|magic|here| |

Snowflake

OUT -> Oracle_02.sql

-- Create Table
CREATE OR REPLACE TABLE blobtable ( blob_column BINARY