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

3 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