Usage of STRTOK_SPLIT_TO_TABLE

Database
K_Y
Enthusiast

Usage of STRTOK_SPLIT_TO_TABLE

Hi

 

does  STRTOK_SPLIT_TO_TABLE works only if table have more than one column ? I have a table which conatins only one column which needs to be broken in rows, tried below query 

 

create multiset volatile table test
(
word varchar(30)
)
on commit preserve rows;

insert into test values ('kavita');

SELECT *
FROM TABLE (STRTOK_SPLIT_TO_TABLE( REGEXP_REPLACE(test.word, '([a-zA-Z])', ',\n'), ',')
RETURNS (outkey VARCHAR(10) CHARACTER SET UNICODE
,tokennum integer
,token VARCHAR(30) CHARACTER SET UNICODE)
) AS dt

 

It fails with error  [Teradata Database] [9881] Function 'STRTOK_SPLIT_TO_TABLE' called with an invalid number or type of parameters

 

However when i add one more column into table as id and include that in my select statement the function works perfectly fine , why so ? please help understanding.

 

Below query worked upon adding id as another column:

SELECT *
FROM TABLE (STRTOK_SPLIT_TO_TABLE(test.id, REGEXP_REPLACE(test.word, '([a-zA-Z])', ',\1'), ',')
RETURNS (id integer
,tokennum integer
,token VARCHAR(30) CHARACTER SET UNICODE)
) AS dt

 


Accepted Solutions
Ambassador

Re: Usage of STRTOK_SPLIT_TO_TABLE

You can directly use REGEXP_SPLIT_TO_TABLE as Waldar mentioned:

 

SELECT *
FROM TABLE (RegExp_Split_To_Table(test.word, test.word, '(?=.)', 'c')
RETURNS (outkey VARCHAR(30) CHARACTER SET Unicode
,tokennum INTEGER
,token VARCHAR(30) CHARACTER SET Unicode)
) AS dt

But what are you actually trying to do?

Do you really want to split a string into characters, why?

 

1 ACCEPTED SOLUTION
6 REPLIES 6
Teradata Employee

Re: Usage of STRTOK_SPLIT_TO_TABLE

Hi K_Y,

 

Indeed, STROK_SPLIT_TO_TABLE needs three inputs parameters.

You can feed a constant if you want for the first one :

select *
  from table (strtok_split_to_table(1, regexp_replace(test.word, '([a-zA-Z])', ',\n'), ',')
              returns ( outkey   integer
                      , tokennum integer
                      , token    varchar(30) character set unicode)
              ) as dt;

The first input is useful to differenciate outputs from multiple lines.

 

Also, if you need some REGEXP_REPLACE, check the function REGEXP_SPLIT_TO_TABLE.

 

 

Ambassador

Re: Usage of STRTOK_SPLIT_TO_TABLE

You can directly use REGEXP_SPLIT_TO_TABLE as Waldar mentioned:

 

SELECT *
FROM TABLE (RegExp_Split_To_Table(test.word, test.word, '(?=.)', 'c')
RETURNS (outkey VARCHAR(30) CHARACTER SET Unicode
,tokennum INTEGER
,token VARCHAR(30) CHARACTER SET Unicode)
) AS dt

But what are you actually trying to do?

Do you really want to split a string into characters, why?

 

K_Y
Enthusiast

Re: Usage of STRTOK_SPLIT_TO_TABLE

Hi Walder

 

Thank you so much for the explanation.

K_Y
Enthusiast

Re: Usage of STRTOK_SPLIT_TO_TABLE

Hi Dnoeth

 

Thanks a lot for the explanation, a few doubts more...

 

1. what does (?=.) stands for in the explanation provided by you, my string doesn't have any delimiter so how the string is getting  delimited using (?=.)

2. what is the difference between STRTOK_SPLIT_TO_TABLE and REGEXP_SPLIT_TO_TABLE ?

 

I'm having a requirement to split striungs into table that why trying to understand functionality of both the functions.

 

Thanks

Kavita

Teradata Employee

Re: Usage of STRTOK_SPLIT_TO_TABLE

1. This is a regexp lookahead assertion, it allows you to grap the regexp identifier (as delimiter) inside the output.

2. STRTOK split according to constant values - like a comma, a pipe, the function accepts multiple delimiters.

REGEXP SPLIT use a regexp to generate delimiters.

 

Ambassador

Re: Usage of STRTOK_SPLIT_TO_TABLE


1. what does (?=.) stands for in the explanation provided by you, my string doesn't have any delimiter so how the string is getting  delimited using (?=.)


The period indicates any character and the ?= lookahead assures that this character is not removed from the result --> splits a string into characters.

 

I'm having a requirement to split striungs into table


Based on which rules? Can you show some example data?