Using LIKE function for matching patterns stored in database
753594Feb 13 2010 — edited Feb 13 2010Hi,
I have referred to the Oracle SQL functions documentation for usage of LIKE function. I am trying to solve a pattern search problem using Oracle SQL.
Problem:
------------
I have got various patterns stored in the database. Every pattern has a weightage associated with it. See e.g. below. Please note that I have used underscore in the pattern value column to signify a single alphanumeric occurence.
pattern_id pattern_value weightage_value
1 SINVPSQ 1000
2 SINV_SQ 800
3 SIN__SQ 400
4 SIN____ 200
5 _______ 100
I have two string values to be pattern matched against the above pattern set.
1st one is SINVXSQ - This matches with patterns 2, 3, 4, 5. I would like to use the 2nd one since this has the maximum weight of the matched patterns.
2nd one is SINRMBA - This matches with patterns 4, 5. I would like to use the 4th one since this has the maximum weight of the matched patterns.
Could anyone advise me how I can go about this using SQL. Please note that the pattern values are stored in the database under database columns and the string values (SINVXSQ, SINRMBA....) are available as variables in the application program.
Any help would be much appreciated.
Best Regards,
Subbu