CODEWITHSUNDEEP
sqlformattingteradatasplit
user3746456
user3746456

Reputation: 21

SQL : Splitting a column into multiple columns, by a common string separator where string has uneven length (TD14)

I have an issue with splitting a variable I am recording, in which various values are separated with a @ sign. I want to separate the values into multiple columns and remove the string separator (@).

NB: The values are usually text, but may be of variable length.

I have both Googled, and searched Stackoverflow numerous times for solutions, but most are not applicable as I have a variable string length and also variable value length :(

Thanks in advance!

Example:

Original column
--------------------------------
Value1@Value2@Value3@Value4@Value5
Value1@Value2@Value3
Value1@Value2@Value3@Value4@Value5@Value6`

What I want:

Column1    Column2     Column3   Column4      Column5   Column6
----------------------------------------------------------------------
Value1     Value2      Value3    Value4       Value5
Value1     Value2      Value3
Value1     Value2      Value3    Value4       Value5    Value6

Upvotes: 2

Views: 7969

Answers (2)

N. Lombardo
N. Lombardo

Reputation: 76

I think you can also use the REGEXP_SPLIT_TO_TABLE function to split the source string into a table with each value as a row.

Upvotes: 0

dnoeth
dnoeth

Reputation: 60513

What's your Teradata release?

TD14 supports STRTOK:

STRTOK(col,'@',1), STRTOK(col,'@',2), STRTOK(col,'@',3), etc.

Upvotes: 2

Related Questions