SQL query with regex in ms-access database

Question

Hi,

I'm working with C# .Net and ms-access database.

I have the following SQL query:

  `Select ... Like "%<aaa>%"+prmPhrase+"%</aaa>%"` 

The query is looking for a phrase inside a database field which contains xml data.

the query works very quickly, but it returns a lot of false results. I can't search only the exact phrase because i have a list of word boundry markers:

  ' ', '-', '.', ':', ',', ';', '/'

for example:

  prmPhrase = run  
  "i run home" -ok  
  "i.run-home" - ok  
  "running" - false result - not ok  

It takes me a lot of time to extract the ok results by code, and return only the ok sentences.

I would like to know if there is anything like regex or something which i could do a better query to return only the ok results without the false ones.

maybe this could help:

http://stackoverflow.com/questions/656951/search-for-whole-word-match-in-sql

Thanks For Advance!

Answer 1

Your task could be solved with something like "%" + SetA + "run" + SetB + "%"
where setA all symbols allowed before phrase (e.g. [a-z]*, to prevent end of tag, so % does not work), setB - all allowed delimiters (e.g. [^a-z0-9]).
I am afraid you could not define SetA with %, _, [] So you should simplify your task.

Answer 2

please read below link :

Regular Expressions Make Pattern Matching And Data Extraction Easier

Answer 3

This doesn't help much in SQL, but Access can use the File System Object's RegExp support. Given the way your data is stored, that's likely not going to be much of a slowdown, since your LIKE matches wouldn't be using any indexes well anyway.