Hi teradata Guru's;
I'm new to teradata. can some one tell me that how can i split address column into multiple columns??
1 house # 1 street # 1 (some more information) City_name
i actually want to split it ....
col1 col2 col3 col4
house# street# other_info city_name
Assuming that you are following a standard convention for splitting your address details (say separated by a comma: ",").
CREATE TABLE test
INSERT INTO test VALUES('House#1, Street#1, CityName');
,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
Output should be LIKE this:
Option1 Option2 Option3
House#1 Street#1 CityName