If a varchar column consists of 2 words seperated by a space, i know how to extract both of them with the help of SUBSTR, LENGTH and INDEX functions. But I am not sure how to do it if the field contains three or more words(not fixed).
Sample Data: Andrew Martin La DEN in a character field
I want to extract them one by one to compare it with other fields.
Can anyone please help me in this.
something like this should work
select 'Andrew Martin La DEN', regexp_substr('Andrew Martin La DEN','[^ ]+',1, day_of_calendar) as a from
(select day_of_calendar from sys_calendar.calendar) b
where day_of_calendar between 1
and length('Andrew Martin La DEN')-length(oreplace('Andrew Martin La DEN', ' ', ''))+1
this is equivalent to
sel regexp_substr('Andrew Martin La DEN','[^ ]+',1,1)
sel regexp_substr('Andrew Martin La DEN','[^ ]+',1,2)
sel regexp_substr('Andrew Martin La DEN','[^ ]+',1,3)
sel regexp_substr('Andrew Martin La DEN','[^ ]+',1,4)
This was inspired satyaki de post in teradata forum, the link is given below
There's a STRTOK function:
strtok(str -- source column
,' ' -- list if delimiting characters
,n) -- extract the nth word = 1,2,3,etc.