Example: Using CSV Data in Double Quotes - Advanced SQL Engine - Teradata Database

DATASET Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
des1556232910526.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1198
lifecycle
previous
Product Category
Teradata Vantageā„¢

Some CSV data feature fields are wrapped in double quotes. The double quotes allow invalid characters (such as record/field delimiters or double quotes) in the value itself. In the following example, CSV_TO_JSON omits the extra leading and trailing double quotes from the output.

SELECT * FROM CSV_TO_JSON
(
	ON (SELECT 'Item_ID,Item_Name,"Item#Color",Item_Style,Quantity_Purchased,Item_Price,Total_Price#55,bicycle,red,boys,1,100.00,100.00#88,toy boat,pink,,1,15.10,15.10#105,soap,,,1,0.99,0.99')
	USING SCHEMA('{"record_delimiter":"#"}')
) AS csvJSON ORDER BY data."Item_ID";

> {"Item_ID":"55", "Item_Name":"bicycle", "Item#Color":"red", "Item_Style":"boys", "Quantity_Purchased":"1", "Item_Price":"100.00", "Total_Price":"100.00"}
> {"Item_ID":"88", "Item_Name":"toy boat", "Item#Color":"pink", "Item_Style":null, "Quantity_Purchased":"1", "Item_Price":"15.10", "Total_Price":"15.10"}
> {"Item_ID":"105", "Item_Name":"soap", "Item#Color":null, "Item_Style":null, "Quantity_Purchased":"1", "Item_Price":"0.99", "Total_Price":"0.99"}