I'm trying to use the REGEXP_SPLIT_TO_TABLE function using SQL Assistant Java Edition on TD 14.10.3.04 and I'm getting the following error:
[Teradata Database] [TeraJDBC 15.00.00.20] [Error 9134] [SQLState HY000] Unexpected failure while attempting to convert UTF8 to UTF16.
I thought it might have something to do with some special characters (that show up as squares in the output to text) in the column I'm running the function on, but when I removed those characters, I'm still getting the error. Has anyone seen this error before and have any idea on what could be causing it?
I do not have SQL assistant. Did you try in bteq?
If you can share the script on what you are doing like
how your table description is....and sample values...
example like how you select...:
I'm thinking there are records in the base table with characters that the process doesn't like, but I'm not sure how to find them.
Looks like the data didn't paste in properly. I'll try again...
CID value - BCBJVDX10001,P1822570,BCBKGY2B0001,BCBK5FM30001,W0Z50624,W0W81370,W0Z50623,
CHLD_ORDER_NO value - 537244
Please provide the CREATE TABLE command for the TEST table.
If your CIRCUITID column is supposed to contain Unicode characters, then it would be challenging to determine which rows contain the problematic characters.
But if your CIRCUITID column is only supposed to contain ASCII characters, then you can use the translate_chk function to find the non-ASCII characters:
translate_chk(CIRCUITID using unicode_to_latin) as e2
where e2 > 0
order by 1
That query returned no results when run on the base table, so it looks like the records are all clean.
I tried the below TEST table as well and I'm only getting results for one of the "NA" records...should I not expect to get results for all of them, since they have different CHLD_ORDER_NO values?
I executed your SQL statements using Teradata Database 15.0 and I got the following result:
res tokennum token
------ -------- ------------
768056 1 NA
537244 1 BCBJVDX10001
768056 2 NA
537244 2 P1822570
768056 3 NA
537244 3 BCBKGY2B0001
768056 4 NA
537244 4 BCBK5FM30001
537244 5 W0Z50624
537244 6 W0W81370
537244 7 W0Z50623
You can try for Non ascii chars check, just to double check:
substr(CIRCUITID , 1,1) >= '80'xc
substr(CIRCUITID , 2,1) >= '80'xc
substr(CIRCUITID , 3,1) >= '80'xc
substr(CIRCUITID , 4,1) >= '80'xc
substr(CIRCUITID , n,1) >= '80'xc
There are various conversion functions like if any for converting........
....and many others
Also, I tested and found that I can enter '0A'xc into a table and can read it too :)