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!
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.
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.
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.