0

I want to concatenate strings from multiple rows. For example, if I have:

ID TEXT
01 Hello
01 hello I have a question
02 Hi!!
02 Thank you

I want to get something like:

ID TEXT
01 Hello; hello I have a question
02 Hi!!; Thank you

I tried the solution provided here, so my query looks like this:

SELECT ID,
TRIM(TRAILING ',' FROM (XMLAGG(TRIM(TEXT)|| ',' ORDER BY TEXT) (VARCHAR(2000)))) as TEXT
FROM my_table
GROUP BY 1;

But now Teradata returns SELECT Failed: The string contains an untranslatable character.

So I tried with the TRANSLATE function:

SELECT ID,
TRIM(TRAILING ',' FROM (XMLAGG(TRIM(
TRANSLATE(OREPLACE(TRANSLATE(TEXT USING LATIN_TO_UNICODE WITH ERROR),'|','') USING UNICODE_TO_LATIN WITH ERROR)
)|| ',' ORDER BY TEXT) (VARCHAR(2000)))) as TEXT
FROM my_table
GROUP BY 1;

But I'm getting the same error.

pedritoanonimo
  • 35
  • 4
  • First you should check which text is failing: `where TRANSLATE_CHK(TEXT USING LATIN_TO_UNICODE WITH ERROR) > 0`. Additionally check nPath: https://stackoverflow.com/a/63685218 (No need for the TRANSLATE as your input seems to be Latin) – dnoeth Feb 10 '23 at 14:15
  • 1
    Apparently your TEXT column is CHARACTER SET LATIN and there were some translation errors when loading the data, so the TEXT contains x'1A' error substitution characters. If for some reason you wanted to retain those, use CHR(44) in place of ',' or if you wanted to change that "bad" character to question mark, for example, you could OTRANSLATE(TEXT,CHR(26),CHR(63)) – Fred Feb 10 '23 at 17:19
  • To explain the error: Character literals like ',' are CHARACTER SET UNICODE so there is an implicit TRANSLATE(TEXT USING LATIN_TO_UNICODE) - not using WITH ERROR option - happening when you concatenate. – Fred Feb 10 '23 at 17:58

0 Answers0