oracle11g - Oracle Text Feature -

i have requirement derive country free form string in oracle (free form string user input , stored in column in database) , store derived value. conditions derive based on either of below scenarios:

1) codes/country/city/zip(only us) country anywhere in string , on occasions, there more 1 hit. 2) country should derived if zip code (for cities) provided in string (a zip-city-state lookup reference table stores information us). 3) country name , corresponding 2 character country code (or iso code) present in string (again stored in lookup reference table). 4) in cases, country directly present in input string.

lets assume simple case of 1 city/country input string is: deutsche bank, unit no. 123, new york

since string contains "new york", country derivation united states (based on lookup information matches country-city).

now, trick when there multiple hits on string: deutsche bank de, unit no.123, taiwan

in above string(a simple multi hit string), there atleast 2 possibilities: de, taiwan (de corresponds germany lookup reference , taiwan spelled out in string). in such cases, there must ranking mechanism weights 2 possibilities based on criteria: lets say, if country spelled out in string, given higher weight code of different country etc. choice of needs given more weight can configurable based on requirements. if later, requirements suggest code given higher weight, should require meta data change alter weight , result should reflect change.

on technical side, have created pl/sql code using regexp , simple string manipulations of these. but, slow , has limitations in covering of these scenarios , frankly, approach don't like. wanted first prototype out door. so, had rush. but, want streamline approach performance there few thousands of input rows(approx 30k) containing these input strings come in everyday , dervation needs happen on fly or atleast batch, @ quicker response rate.

i reading oracle text feature design approach , pleasntly surprised of features have been seeking. however, not sure start have not used oracle text feature before although have started reading on it. pointers experts appreciated.

environment: oracle 11g.

thanks, casey