Restrict records based on number of words in string field

[ksnpc]

I'm using CR 9 with an Oracle database.

I have a string field that contains scientific names. This is an example of my current output:

Bufo americanus
Bufo americanus americanus
Bufo americanus charlesmithi
Gyrinophilus porphyriticus
Gyrinophilus porphyriticus duryi

However, I want to exclude records that have names made up of more than two words. So from the list above I only want to get:

Bufo americanus
Gryinophilus porphyriticus

In theory I should be able to do this by restricting the names that have more than one space so only names with 2 words would be included. Unfortunately, I can't quite get it to work. Could anyone set me straight? Or can you think of a more effecient way to do the same thing?

Thanks,
Shauna

 

[lbass]

Try:

len({table.string}) - len(replace({table.string}," ","")) < 2

Or you could use:

ubound(split({table.string}," "})) < 3

-LB

 

[synapsevampire]

You could create a SQL Expression in Oracle akin to:

decode(instr(trim(substr(prpaycode.description,instr(prpaycode.description,' ')+1)),' '),null,0,instr(trim(substr(prpaycode.description,instr(prpaycode.description,' ')+1)),' '))

Replace prpaycode.description with your field.

This would return a value of where the second space is in a word, so you would then use something akin to the following in the Report->Edit Selection Formula->Record:

{%MySQLExpression} = 0

If you know that the data has more than one space between words, than you can wrap the field in a replace statement as in LB's example, however if there are 3 spaces it won't resolve, the same problem as with either of his examples.

You might do a global Update against the database first to replace all of the additional spaces using a series of wrapped REPLACE statements first to fix the data.

Anyway, this will offload all of the processing to the database, eliminating the rows from the result set and be the most efficient means as the above examples won't pass the SQL to the database.

-k

 

[synapsevampire]

Opps, this:

This would return a value of where the second space is in a word,

should be:

This would return a value of where the second space is in a FIELD

-k

 

[ksnpc]

I really appreciate the responses! Both solutions worked very well although the SQL Expression solution was slightly faster. Thanks very much to both of you!