more elegant way of splitting a delimited column?

Database
Enthusiast

more elegant way of splitting a delimited column?

Hi all, I've developed the following solution, which will be a part of a much more complex query. I was wondering if there might be a cleaner way of solving the problem than what I've currently come up with. The  gl_code field will typically have values that look like 1234*5678*2345*4678. This splits that value into the 4 distinct fields instead. I'm hoping there may be a way with regex, but I haven't had to use it in years, and have little experience with regex in SQL or teradata

 

select distinct gl_code
,max(case when dt.tokennum = 1 then dt.token end) as "1"
,max(case when dt.tokennum = 2 then dt.token end) as "2"
,max(case when dt.tokennum = 3 then dt.token end) as "3"
,max(case when dt.tokennum = 4 then dt.token end) as "4"


from cl_gl_transaction gl
inner join (
select * from table (
STRTOK_SPLIT_TO_TABLE(cl_gl_transaction.GL_TX_ID, cl_gl_transaction.gl_code, '*')
RETURNS (outkey VARCHAR(30) CHARACTER SET UNICODE
,tokennum INTEGER
,token VARCHAR(30) CHARACTER SET UNICODE)
) AS dt ) dt on gl.gl_tx_id = dt.outkey

group by 1

7 REPLIES
Enthusiast

Re: more elegant way of splitting a delimited column?

I realize I posted this topic on a bad day. Sorry to bump the thread.

Junior Contributor

Re: more elegant way of splitting a delimited column?

If it's a small number of columns the easiest way is STRTOK:

WITH cte AS
 (
   SELECT 1 AS GL_TX_ID, '1234*5678*2345*4678' AS gl_code
 )             
SELECT GL_TX_ID, 
   StrTok(gl_code, '*', 1),
   StrTok(gl_code, '*', 2),
   StrTok(gl_code, '*', 3),
   StrTok(gl_code, '*', 4)
FROM cte;

Another way utilizes CSVLD:

WITH cte AS
 (
   SELECT 1 AS GL_TX_ID, '1234*5678*2345*4678' AS gl_code
 )             
SELECT *
FROM TABLE (CsvLd(cte.gl_code, '*', '')
     RETURNS ("1" VARCHAR(30) CHARACTER SET Unicode
             ,"2" VARCHAR(30) CHARACTER SET Unicode
             ,"3" VARCHAR(30) CHARACTER SET Unicode
             ,"4" VARCHAR(30) CHARACTER SET Unicode)
           ) AS T1;

Caution, the result will be different when you got an input string like '1234**2345*4678', STRTOK is a tokenizer and treats multiple delimiters as one, resulting in 1234,2345,4678,NULL while CSVLD returns 1234,NULL,2345,4678

 

 

 

Enthusiast

Re: more elegant way of splitting a delimited column?

Thanks! strtok worked perfectly, as i need to filter out "bad" strings, as I don't want any of the fields to be null (or filled with blank spaces, which sometimes happens).

select distinct gl_code
,strtok(gl_code,'*',1) as GL_CHARTFIELD
,strtok(gl_code,'*',2) as LOC
,strtok(gl_code,'*',3) as DEPT
,strtok(gl_code,'*',4) as ACCT
from cl_gl_transaction
where GL_CHARTFIELD > 0 and LOC > 0 and DEPT > 0 and ACCT > 0

Fbo
Fan

Re: more elegant way of splitting a delimited column?

Hello, need some advise please, i have a table with :

Name, Entity

Joe,12|13|25

Jane,1|24|32|56

Louis,4

...

and i need to transform them like this :

Joe,12

Joe,13

Joe,25

Jane,1

Jane,24

Jane,32

Jane,56

Louis,4

 

The delimiter is '|'

How can i SPLIT this field in a temporary table to join it with another table ?

Thank you for advance for any Help.

 

Teradata Employee

Re: more elegant way of splitting a delimited column?

I would consider any table that contains a multi-value delimited column the temporary table! The table you are calling the temporary table would be the permanent table in my database. But I suppose that's beside the point....

 

You would first use the Regexp_Split_To_Table function to break the delimited values into separate columns (e.g. Entity1, Entity2, ...) and put those in a temporary table. Then use the TD_UnPivot function to normalize the multiple Entity values from that table into multiple rows in your target table.  The SQL Functions manual, available at info.teradata.com, documents these two functions and shows examples that should make this all very clear.

 

To go even further, since these functions are both table operators, it should be possible to do all this in one SQL statement by nesting the two table operators - then you wouldn't need the temporary intermediate table.  But for a first cut you might want to do it in two steps so you can check the results.

Fbo
Fan

Re: more elegant way of splitting a delimited column?

Thank You GJColeman for you response, in fact i use strok_split_to_table function and it seems do to the job.

But now i face another problem :

I try to make this work but i got an error

 

WITH T1 as

 SELECT d.* FROM TABLE (strtok_split_to_table( Utilisateurs.Id ,  Utilisateurs.Entites, '|')
RETURNS (Id INTEGER, tokennum integer, Entites varchar(20)character set unicode) ) as d
)
WITH T2 as

 SELECT d1.* FROM TABLE (strtok_split_to_table( Utilisateurs.Id ,  Utilisateurs.RegroupementsDeControles, '|')
RETURNS (Id INTEGER, tokennum integer, RegroupementsDeControles varchar(20)character set unicode) ) as d1
)
select T1.*
from   T1
join T2
on T1.Id = T2.Id  
order by T1.Id
select T1.*
from   T1
join T2
on T1.Id = T2.Id  
order by T1.Id

Fbo
Fan

Re: more elegant way of splitting a delimited column?

OOOOPS i just read it again and i saw the second WITH :) Now it's work