January 12, 2009 at 9:54 am
I am trying to create a function that will search through a field and look for contents in a specific table. If it finds something in that table it will then remove it from the text string...
For example....
lets use a field called description
that field contains this text: this is a brown leather jacket from siberia
I have a table of colors...
I want to loop through the table of colors, if brown is in that table I want to remove brown from the text string...
I know how to use replace...
description= replace(description,@color)
What I don't know is the best way to create an array from a table and loop through it.
My end goal here is to create varius tables that hold arrays of key words that need to me removed from descriptions or added to other fields...
The only other way I can think of to do this is by building infinitly long case statements.
January 12, 2009 at 10:55 am
Parse the statement (the description) into individual words, one word per row. You can parse on spaces, then separate punctuation marks into their own rows. (The scripts section of this page has scripts for string parsing. Use one that utilizes a Numbers/Tally table, since those are more efficient.)
Then, join the parsed string to the table of words you want to remove, and select the ones with no join. (Left outer, where no data in joined column.)
Then rebuild the statement from that.
If you need help with that, please let us know here.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2009 at 12:55 pm
Thanks,
I will try that.
Would this be an efficient way to go through say 100,000 descriptions and joining on a table of hundreds of words?
January 12, 2009 at 12:57 pm
It's the most efficient way I know of for that. You might have a better option with full text indexing. I don't know enough about that to say one way or the other, but it might be worth looking into.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply