question

sachingupta-1921 avatar image
0 Votes"
sachingupta-1921 asked Sumarigo-MSFT edited

Treating Special and Escape charaters while loading data into SQL Table

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.

@SaurabhSharma-msft, @MartinJaffer-MSFT, @ShaikMaheer-MSFT

Thanks

azure-data-factoryazure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered SaurabhSharma-msft commented

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.






· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@AlbertoMorillo ... I want to know how we can handle this using SQL instead of T-SQL?
Also about the special character...do we have a way to handle those special characters before writing the data into BLOB without using any code?

0 Votes 0 ·

Hi @sachingupta-1921,

I believe you have got your answer over here

Thanks
Saurabh

1 Vote 1 ·