Select by regex

[chrisjoha]

Is it possible to store a regex pattern in a database table and then select the most relevant row to a given string?

Table:

pattern__¦_data  
--------------------------------- 
ex*______¦_Some text 
example*_¦_This is an example 
--------------------------------- 

(please ignore the _'s, I cannot seem to achieve preformatted text)

and then a query like

SELECT data from tablename where [match('extra', pattern)] order by [most relevant] limit 1;

would return "Some text" and a query like

SELECT data from tablename where [match('example text', pattern)] order by [most relevant] limit 1;

would return "This is an example".

Is it possible? If so - how?

[chrisjoha]

Can anyone offer some hints on this one?

[stu_uk]

In short no this isnt possible with pure SQL.

[emsaw]

I don't have any experience with it, but for MySQL 5.0, regular expressions can be used according to the documentation:
[dev.mysql.com ]

for MS SQL Server 2000, there aren't 'real' regular expressions, but you can probably come up with a simple expression by using LIKE and some pattern matching constructs, i.e. '[xyz][abc]' will match any two letter combination of x, y or z followed by an a, b or c.. look in your SQL Server Books Online for 'pattern searching'

HTH,

mark

[syber]

MS SQL 2000 has something similar with its Full-Text Indexing option.

With this feature you can using the CONTAINS or CONTAINSTABLE predicates do the following kinds of searches:

Prefix Term - CONTAINS(ProductName, ' "choc*" ')

Proximity - CONTAINS(ProductName, 'spread NEAR Boysenberry')

Inflectional - CONTAINS(ProductName, ' FORMSOF (INFLECTIONAL, dry) ')

Weighted - CONTAINS(Description, 'ISABOUT (spread weight (.8), sauces weight (.4), relishes weight (.2) )' )

example of CONTAINSTABLE with ranking 
 
SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK 
FROM Categories AS FT_TBL INNER JOIN  
 CONTAINSTABLE(Categories, Description,  
 'ISABOUT (breads weight (.8),  
 fish weight (.4), beers weight (.2) )' ) AS KEY_TBL 
 ON FT_TBL.CategoryID = KEY_TBL.[KEY] 
ORDER BY KEY_TBL.RANK DESC 

[chrisjoha]

Ok, thanks for your input. Guess I'll add a priorities column or something to simulate "best match".