Treating Special and Escape charaters while loading data into SQL Table

sachin gupta 376 Reputation points
2021-09-21T17:43:34.773+00:00

Hello,

I am calling a REST API and loading the data into Blob container CSV file. One column has some values in Japanese and another column has values with escape characters. I have been suggested below approaches

1) Set the column value to NULL or BLANK wherever we get the Japanese characters - How to do this?
2) Remove the entire record which has Japanese value for any column - How to do this also?
3) How to treat the columns which has escape characters before storing the data into CSV?

Kindly suggest some ideas on this issue.

@Saurabh Sharma , @MartinJaffer-MSFT , @ShaikMaheer-MSFT

Thanks

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,542 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,886 Reputation points MVP
    2021-09-21T18:28:09.173+00:00

    You may try to do the following assuming you can store data coming from CSV on a UNICODE varchar data type like fiedl1 on below example: 

    DECLARE @Data TABLE (Field1 NVARCHAR(100))
INSERT @Data VALUES ('ABC')
INSERT @Data VALUES (N'Value with 化ける unicode chars in')
SELECT * FROM @Data WHERE Field1 <> CAST(Field1 AS VARCHAR(100))

    The above T-SQL code should return all rows with Japanese rows, then you can decide what to do with them.

    About how to skip special characters or escape characters coming from CSV files please read this thread for a solution.