Find prepositions in a string

hitmanthesilentassasin · Thu Dec 19, 2013 5:49 pm

Hi,

I was working on finding all prepositions in a string. I have tried the index method but it doesnt work when there are multiple words in the string. So here is what I am trying to achieve.

Code: Select all

source string
1. " I want to know the code"
2. "tobey"

Expected Results: I want to select the first record because it has the word "to" in it. Similarly I would want to identify rows with other prepositions like if, for, to, from etc. Although the second string has got the substring "to" in it, I wouldn't want to select that.

chulett · Post by **chulett** » Thu Dec 19, 2013 9:14 pm

The trick to avoiding substring matches is to use delimiters, in this case a space. Add one on each end of the full string and each search term:

Search for " to " within " " : source_string : " "

hitmanthesilentassasin · Thu Dec 19, 2013 9:50 pm

Thanks Craig!!

The source string is not restricted to one word which is causing me the trouble. there are multiple words coming from the source and I have a list of preposition words that I need to identify. Is there any other technique that I can apply here?

chulett · Post by **chulett** » Thu Dec 19, 2013 10:01 pm

Nothing I posted restricts the source string to only a single word. What kind of trouble are you having? Can you post some examples?

hitmanthesilentassasin · Thu Dec 19, 2013 10:18 pm

Below is the function I have defined

Code: Select all

index('| of | in | to |', '| ' : sourcecolumn : ' |',1 )

with the above function I am getting a match when the source column is having a single word like "IF" or "TO" but it wouldn't match it when the word is in between a string like "I want to enjoy Christmas"

ray.wurlod · Post by **ray.wurlod** » Fri Dec 20, 2013 11:58 am

Can't be done this way. You have to search for each preposition separately. Use Transformer stage looping.

chulett · Post by **chulett** » Fri Dec 20, 2013 4:28 pm

And don't use pipes unless your values are pipe delimited in your source string. In other words, match what is existing in both what you tack on each end of the source string and what you wrap each word with. Your example showed spaces, hence my posted example. And then as Ray noted, you need to do each word separately. If you don't want to go looping, set up a stage variable for each.