STRTOK_SPLIT_TO_TABLE function displaying only half of the data on table level from a sentence.

General
K_Y
Enthusiast

STRTOK_SPLIT_TO_TABLE function displaying only half of the data on table level from a sentence.

Hi

 

I have a scenerio where i've to count the words contained in a sentece, senetece can be very long and of variable lenth, Data is like below separted by ,

 

["AB-AC_N","AB-AC_S","AB-AR_E","AB-AR_W","AB-CT","AB-DE","AB-DC","AB-FL_C","AB-FL_NW","AB-FL_S"]

 

I want to display like below:

 "AB-AC_N"

"AB-AC_S"

 

Tried using STRTOK_SPLIT_TO_TABLE function and it works really well for small senetece like above but giving only half of the value for too long sentece.

 

Tried below query:

 

CREATE TABLE db.table1 AS (

SEL cast(X.OUTKEY AS DECIMAL(38,0)) ID , TOKENNUM as position_in_sentence , TOKEN as region from
(SELECT D.* FROM TABLE (STRTOK_SPLIT_TO_TABLE( CAST(table2.ID AS VARCHAR(38)), OTRANSLATE(table2.REGION_LIST,'[]',''), ',')

RETURNS (OUTKEY VARCHAR(38) , TOKENNUM INTEGER, TOKEN VARCHAR(20)CHARACTER SET UNICODE) ) AS D ) X group by 1,2,3) WITH DATA ;

 

Is there anyway to get the full value displayed from a long sentence basically I can count those words if I'am able to displaye on table level.

 

Thanks

Kavita

Tags (1)
1 REPLY
Teradata Employee

Re: STRTOK_SPLIT_TO_TABLE function displaying only half of the data on table level from a sentence.

Perhaps it's due to the OTRANSLATE and not the STRTOK_SPLIT_TO_TABLE? Some sites have old (and often more limited) versions of functions such as OTRANSLATE installed in SYSLIB. Try explicitly qualifying TD_SYSFNLIB.OTRANSLATE(...).

 

(And if that works, talk to your DBA about possibly removing the old function.)