convert column data to rows based on length

Database
Enthusiast

convert column data to rows based on length

Hi,

I am trying to convert one column data into rows based on the fixed length. 

 

Ex: 

Table A 

ColumnA

1ABCD2XYZA3WSXE4QAZW

1BCDE2YZAB3SXEF4AZWQ

 

Output required 

ColumnA

1ABCD

2XYZA

3WSXE

4QAZW

1BCDE

2YZAB

3SXEF

4AZWQ

 

Any ponter would help 

 

Thanks

Muzammil 

 

4 REPLIES 4
Teradata Employee

Re: convert column data to rows based on length

Hi Muzammil,

 

A combination of regexp_replace and strtok_split_to_table does the job :

with cte_data (id, str) as
(
select 1, '1ABCD2XYZA3WSXE4QAZW' from cte_dual union all
select 2, '1BCDE2YZAB3SXEF4AZWQ' from cte_dual
)
  ,  cte_dual (dummy) as (select 'X')
select t.*
  from table(strtok_split_to_table(cte_data.id, regexp_replace(cte_data.str, '(.{5})', '\1,'), ',')
             returns (outkey integer, tokennum integer, token varchar(20)character set unicode)) as t;

1	1	1ABCD
1	2	2XYZA
1	3	3WSXE
1	4	4QAZW
2	1	1BCDE
2	2	2YZAB
2	3	3SXEF
2	4	4AZWQ
Teradata Employee

Re: convert column data to rows based on length

Hi Waldar.

 

One a little bit less complicated with arithmetic and an iterator (for a fixed length text with chunks of 5 chars):

 

BTEQ -- Enter your SQL request or BTEQ command:
SELECT SUBSTR( pre.TheString, 1 +( (cal.day_of_calendar - 1 ) * 5), 5) TheChunk
  FROM (
         select TheRow, '1ABCD2XYZA3WSXE4QAZW' TheString from (select 1 TheRow) a
          union all
         select TheRow, '1BCDE2YZAB3SXEF4AZWQ' TheString from (select 2 TheRow) b
       ) pre,
       SYS_CALENDAR.CALENDAR cal
 WHERE cal.day_of_calendar BETWEEN 1 AND 4
 ORDER BY pre.TheRow, TheChunk
;


*** Query completed. 8 rows found. One column returned.
*** Total elapsed time was 1 second.

TheChunk
--------
1ABCD
2XYZA
3WSXE
4QAZW
1BCDE
2YZAB
3SXEF
4AZWQ

 

;-)

 

Cheers.

 

Carlos.

Highlighted
Teradata Employee

Re: convert column data to rows based on length

You're 100% right !

One can even expand on instead of doing a calendar join.

Data

create multiset volatile table mvt_datas, no log
( mvt_pi    byteint     not null
, id        byteint     not null
, str       varchar(30) not null
)
primary index (mvt_pi)
on commit preserve rows;

insert into mvt_datas values (0, 1, '1ABCD2XYZA3WSXE4QAZW');
insert into mvt_datas values (0, 2, '1BCDE2YZAB3SXEF4AZWQ');

collect statistics column (mvt_pi) on mvt_datas;

 

Query

with cte_expand (id, str, num_line, start_sub) as
(
select id, str, current_date - begin(expd) as num_line
     , 1 + 5 * (num_line - 1) as start_sub
  from mvt_datas
 where mvt_pi = 0
expand on period(current_date -  ceil(length(str) / 5), current_date) as expd by interval '1' day
)
  select id, num_line
       , substring(str from start_sub for 5)
    from cte_expand
order by 1, 2;

 

 

Ambassador

Re: convert column data to rows based on length

You can directly apply REGEXP_SPLIT_TO_TABLE:

WITH cte_data (id, str) AS
(
SELECT 1, '1ABCD2XYZA3WSXE4QAZW' FROM cte_dual UNION ALL
SELECT 2, '1BCDE2YZAB3SXEF4AZWQ' FROM cte_dual
)
  ,  cte_dual (dummy) AS (SELECT 'X') 
SELECT t.*
  FROM TABLE(RegExp_Split_To_Table(cte_data.id, cte_data.str, '(.{5}\K)', '')
             RETURNS (outkey INTEGER, tokennum INTEGER, token VARCHAR(20)CHARACTER SET Unicode)) AS t
;