How to Split Column

Database

How to Split Column

Hi teradata Guru's;

I'm new to teradata. can some one tell me that how can i split address column into multiple columns??

eg

s_id                                   address

1                                       house # 1 street # 1 (some more information) City_name

i actually want to split it ....

e.g

col1               col2             col3                col4

house#         street#       other_info      city_name

Help please..

thanx!!

Mubin

Tags (1)
1 REPLY
Teradata Employee

Re: How to Split Column

Assuming that you are following a standard convention for splitting your address details (say separated by a comma: ",").

CREATE TABLE test
(
col1 VARCHAR(500)
)
;

INSERT INTO test VALUES('House#1, Street#1, CityName');

SELECT
option1
,option2
,option3
FROM
(
SELECT col1
,POSITION(',' IN col1) AS pos1
,SUBSTRING(col1 FROM 1 FOR pos1-1) AS option1
,SUBSTRING(col1 FROM pos1+1 FOR CHARACTER(col1)) AS substr1
,POSITION(',' IN substr1) AS pos2
,SUBSTRING(substr1 FROM 1 FOR pos2-1) AS option2
,SUBSTRING(substr1 FROM pos2+1 FOR CHARACTER(substr1)) AS option3
FROM test
)T;

Output should be LIKE this:

Option1       Option2     Option3

House#1      Street#1    CityName