Pull 2nd word out of a text attribute

tford

I'm in a V6 BSV and trying to pull the last name out of an attribute where the last name is the 2nd word. WORDS_FROM_LEFT would be perfect but I see in the User Guide that it is only supported in Derby.

Does anyone have an alternative way to calc via rule?

The following functions are supported only in Cloudscape/Derby database:

WORD_NUMBER

WORDS_FROM_LEFT

WORDS_FROM_RIGHT

intra

Hi Tom,

Lets assume

String = 'John Smith is very cool!' (we want to pull the second word right?)

So a function that could accomplish this would be.

SUBSTRING(String, INDEX_OF(String, ' ',1), INDEX_OF(String, ' ',2)) which should convert to SUBSTRING(String, 5,11)

This should cut out the required second word which is 'Smith'

rob_h7

We use this in MYSQL & works fine.

IF WORD_NUMBER(Candidate.CandidateName)=2 THEN Candidate.FirstName=WORDS_FROM_LEFT(Candidate.CandidateName,1)
Candidate.LastName=WORDS_FROM_RIGHT(Candidate.CandidateName,1)

We never saw the 'only works in Derby' so threw it in, I guess if you don't read it, the disclaimer doesn't apply. 😮