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
,token VARCHAR(30) CHARACTER SET UNICODE)
) AS dt ) dt on gl.gl_tx_id = dt.outkey
group by 1
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
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
where GL_CHARTFIELD > 0 and LOC > 0 and DEPT > 0 and ACCT > 0
Hello, need some advise please, i have a table with :
and i need to transform them like this :
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.
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.
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
on T1.Id = T2.Id
order by T1.Idselect T1.*
on T1.Id = T2.Id
order by T1.Id