Deconcatenation of column into multiple columns

Database

Deconcatenation of column into multiple columns

Hi I am pretty new to Teradata and already facing one big challenge:

I need to deconcatenate column to multiple separate columns.

My column COLUMN contain string which looks like this one
value1~+~value2~+~value3~+~value4 and I need to deconcatenate results to 4 separate columns COLUMN1 with value1, COLUMN2 with value2, ...

Values 1 to 4 have variable lenght, delimiter in this case is ~+~

Do you have any idea where to start, any guidance?

Thanks,

m.

Tags (2)
3 REPLIES
Enthusiast

Re: Deconcatenation of column into multiple columns

The following will break down your string into a maximum of 4 fields.
If you want to go to an infinite length, you need to go to an SP to allow for looping.

Create Volatile Table T1
(PK Smallint Not Null
, AllCols Varchar(60) Not Null)
Unique Primary Index (PK)
On Commit Preserve Rows;

Insert Into T1 Values (1,'value1~+~value2~+~value3~+~value4 ');
Insert Into T1 Values (2,'value21~+~value22~+~value23');
Insert Into T1 Values (3,'value100~+~value20000~+~value300000~+~value499999 ');
Insert Into T1 Values (4,'value199~+~value28888888');
Insert Into T1 Values (5,'valueLot');

Select * From T1
Order By 1;

Select
PK, AllCols
, Case When Col1Len = -1 Then AllCols Else Substring(AllCols From 1 For Col1Len) End As Value1
, Case When Col1Len = -1 Then Null Else Substring(AllCols From Col1Len + 4 For (Case When Col2Len = -2 THEN 99 Else Col2Len End)) End As Value2
, Case When Col2Len = -2 Then Null Else Substring(AllCols From Col1Len + Col2Len + 7 For (Case When Col3Len = -2 Then 99 Else Col3Len End)) End As Value3
, Case When Col3Len = -2 Then Null Else Substring(AllCols From Col1Len + Col2Len + Col3Len + 10) End As Value4
From
-- Get the start position for all substrings
(Select PK, AllCols
, Position('~+~' in AllCols)-1 As Col1Len
, Position('~+~' in Substring(AllCols From Col1Len + 3))-2 As Col2Len
, Position('~+~' in Substring(AllCols From Col1Len + Col2Len + 6 ))-2 As Col3Len
, Position('~+~' in Substring(AllCols From Col1Len + Col2Len + Col3Len + 9 ))-2 As Col4Len
From T1) As D1
Order By 1
;

Sorry about the formatting - paste into SQL Assistant and it should look better!

Re: Deconcatenation of column into multiple columns

Jimm,

Thank you very much, your code works like charm. I have exactly 4 concatenated values in my column so it fits to my needs.

For future use, not sure if I get what you mean clearly. What means SP abbreviation?

Thanks again.
Appreciate your quick respond.

m.
Enthusiast

Re: Deconcatenation of column into multiple columns

SP is stored procedure. It allows looping in SQL, so it allows you to code (in this case) an endless loop taking a value and looking for a sbsequent value form that point. (There are other features of SP's but that s the relevant feature for this kind of operation.)

If you have exactly 4 values every time, you can simplify and remove most of the Case statements - these are there to handle less than 4 values.
If there is ANY chance there is less than 4, leave them in - removing them will gave an incorrect value where there is no value present.