The emoji characters look like the one in the following link
I am not able to paste the charatcer in the forum hence providing the link
First, why are you trying to load UTF8 data using a character set of ASCII? And why would you want a staging table with Latin columns?
The UTF8 code points will most likely all be out of the range of ASCII.
Second, please give an example of your delimited input file. The character sequence <d83d> will not result in the data being converted to a character to be loaded. Delimited data represented in hex format will not be interpreted as an emoji character. The data is VARCHAR and all of the characters are just a sequence of bytes.
When you use FastLoad to load a delimited file, the schema is made up of all VARCHAR fields. Thus, the characters <dd30> just represent a character string. There is no way for FastLoad to convert them.
Yes, FastLoad can load into a table with a character set of UTF8. But again, these are just a sequence of characters, They are character strings.
So, if you have a way to convert (via UDF) <dd30> to something that is in the format you desire, that would be the correct approach.
In BTEQ, have you tried "UTF8" as the character set name, and not "UTF-8"? (I do not know the correct syntax for BTEQ. In TPT and the load utilities we use "UTF8", not "UTF-8".)
Thanks a lot for the insight
Now if i were to get 'F09F94B0'XC in my file instead of <d83d><dd30>
will then i be able to use fastload and load the table and avoid UDF ?
i have tried loading 'F09F94B0'XC manually in to a latin column and when i select that column it displays the graphic character :)
Looking for your inputs