Punctuation mucking up DataView RowFilter and LIKE statement

[mortovski]

Hi,

The Background
I'm working on a Bible searching application, and I have a DataTable with a whole lot of verses in it that I'm wanting to search. So I'm using a DataView with a RowFilter to search through the text to return rows with a particular word. A sample of the code is as follows:

public int SearchVerses(string searchtext)
{
	searchtext = searchtext.Replace("'","\\'");
	ArrayList verselist = new ArrayList();
	string verseid="";
		
	string selecttext = "VerseText LIKE '%" + searchtext + "%'";
						
	DataTable dtverselist = bereanmain.res.GetCurrentVerseList;
			
	DataView dvverselist = new DataView(dtverselist,selecttext,"VerseID",DataViewRowState.CurrentRows);
			
	foreach (DataRowView drv in dvverselist)
	{
		verseid = drv[0].ToString();
		verselist.Add(verseid);
	}

The Problem
The trouble is that if I search for a text such as amazing then it won't retrieve amazing. or amazing! - the punctuation keeps mucking up the return results.

Is there any way to fix this, or is there a better way to approach this?

Thanks,
Peter

[GaryTexmo]

Well, I have two ideas for you...

1) Strip the punctuation from your verse text before you query. If you just want to match on key words, you're not necessarily interested in the punctuation. If you want the punctuation returned with the query, you could always create another field with the verse text with stripped punctuation, and with the verse text as normal. Search the stripped text and return the normal text.

Yea, it effectively doubles your storage space, but it's quick and easy.

2) Another alternative may be to try a regex query.

http://www.postgresql.org/docs/8.3/static/functions-matching.html

See the section on SIMILAR TO.

Good luck!

[mortovski]

Hey thanks so much for that. The secondary field idea would work well - and seeing as the verse databases are static it would be a once-only operation. I've never really had a lot of luck with regex expressions so I'll give the first idea a try i reckon.

Cheers,
Peter

[GaryTexmo]

Yea, regular expressions aren't my strong suit either. Fortunately, there's a handy dandy flash util that I've found extremely useful for setting these up.

http://gskinner.com/RegExr/

You can build your expression on top to match the text on the bottom. The panel on the right contains common expressions you can insert and they even tell you what they do. You can also hover over a block of text in your expression input and it will tell you what it does.

All the best,
Gary