String 'abcdfghrdfthgywdcgrt' is too long and would be truncated

Question

Query:

CREATE TABLE SRC(SRC_STRING VARCHAR(20)) CREATE OR REPLACE TABLE TGT(tgt_STRING VARCHAR(10))

INSERT INTO SRC VALUES('JKNHJYGHTFGRTYGHJ')

INSERT INTO TGT(TGT_STRING) SELECT SRC_STRING::VARCHAR(10) FROM SRC

Error: String 'JKNHJYGHTFGRTYGHJ' is too long and would be truncated

Is there any way we can enable enforce length(not for COPY command) while inserting data from high precision to low precision column?

Copying data from high varchar length column to low varchar length column is not an issue. What are you looking for ? — Narsireddy
updated the description, it's about sql INSERT statement not COPY command. — rakesh singh

Rich Murnane Rich Murnane · Accepted Answer · 2020-08-10T13:18:02

I'd recommend using the SUBSTR( ) function, to pick the piece of data you want, example as follows where I take the first 10 characters (if available, if there were only 5 it'd use those 5 characters).

CREATE OR REPLACE TEMPORARY TABLE SRC(
    src_string VARCHAR(20));

CREATE OR REPLACE TEMPORARY TABLE TGT(
    tgt_STRING VARCHAR(10));

INSERT INTO src 
VALUES('JKNHJYGHTFGRTYGHJ');

INSERT INTO tgt(tgt_string) 
SELECT SUBSTR(src_string, 1, 10) 
FROM SRC;

SELECT * FROM tgt; --JKNHJYGHTF

Here's the documentation on the function:

https://docs.snowflake.com/en/sql-reference/functions/substr.html

I hope this helps...Rich

p.s. If this (or another) answer helps you, please take a moment to "accept" the answer that helped by clicking on the check mark beside the answer to toggle it from "greyed out" to "filled in".

String 'abcdfghrdfthgywdcgrt' is too long and would be truncated

1 Answers