MSC-PG0002

Bpchar converted to varchar

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

This warning is added because bpchar type (“blank-padded char”) may have some functional equivalence difference compared to the varchar data type in Snowflake. However, both data types can store the values up to the “n” length of characters and consume storage for only the amount of actual data stored. The main difference occurs when there are blanks at the end of the data, where bpchar does not store them but snowflake does.

For this reason, we can use the RTRIM function so that these blanks are not stored. But there may be cases where the functionality is not completely equivalent.

Code Example

Input Code:

Column Definition

CREATE TABLE table1 (
    col1 BPCHAR, 
    col2 BPCHAR(20)
);

Explicit Cast

SELECT 'Y'::BPCHAR;

SELECT 'Y   '::BPCHAR(20);

SELECT COL1::BPCHAR(20) FROM tbl;

Output Code:

Column Definition

CREATE TABLE table1 (
col1 VARCHAR /*** MSC-WARNING - MSC-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES ***/,
col2 VARCHAR(20) /*** MSC-WARNING - MSC-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES ***/
);

Explicit Cast

SELECT 'Y':: VARCHAR /*** MSC-WARNING - MSC-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES ***/;

SELECT RTRIM( 'Y   ') :: VARCHAR(20) /*** MSC-WARNING - MSC-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES ***/;

SELECT
       RTRIM( COL1) :: VARCHAR(20) /*** MSC-WARNING - MSC-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES ***/ 
FROM
       tbl;

Recommendations

• The rtrim function can resolve storage differences in case you want those blanks not to be stored. This case is handled in the explicit cast, however, there may be other scenarios where it has to be handled manually. For more information refer to the Snowflake documentation about RTRIM.

• If you need more support, you can email us at [e-mail]