extract words from a field

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
a19
Fan

extract words from a field

Hi,

 

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.

 

 

4 REPLIES
Junior Supporter

Re: extract words from a field

Hi

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

http://community.teradata.com/t5/Database/Regular-Expression-On-Teradata-14-0/m-p/25568#M11541

Senior Apprentice

Re: extract words from a field

There's a STRTOK function:

strtok(str   -- source column
,' ' -- list if delimiting characters
,n) -- extract the nth word = 1,2,3,etc.

Dieter

Junior Supporter

Re: extract words from a field

simpler and easier strtok is much better

a19
Fan

Re: extract words from a field

Thanks @dnoeth :)

Thanks @nealvenna :)

Tags (1)
  • Tags: