• Welcome, Guest. Please login.
 
October 19, 2021, 12:23:16 AM

News:

Welcome to the SQLitening support forums!


Using SQL to search a table for text

Started by Bern Ertl, March 03, 2009, 06:20:39 PM

Previous topic - Next topic

Bern Ertl

I've got code equivalent to the following:

SELECT RowID from T1 WHERE F1 = 'VALUE' OR F2 = 'VALUE'

This searches the table to see if any fields in two columns match a given value.  This works, but only returns exact matches.  I would like to modify the statement so that it returns records if there is a partial match (ie. if 'VALUE' is contained within data stored in F1 or F2).  If it's possible to specify case insensitive matching, that would be great too.

I'm looking at the SQLite documentation and the WHERE clause of the SELECT statement expects an [exp] which is defined thusly:



It seems like there might be opportunity to use the MATCH or REGEXP features, but I can't find any documentation on them.  Help?

Bern Ertl

Well, I just found this page explaining expressions which states:
QuoteThe REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If a user-defined function named "regexp" is added at run-time, that function will be called in order to implement the REGEXP operator.

The MATCH operator is a special syntax for the match() user function. The default match() function implementation raises and exception and is not really useful for anything. But extensions can override the match() function with more helpful logic.

How do I add a user defined function for REGEXP and/or MATCH?

Bern Ertl

More digging... Looks like I will need to use sqlite3_create_function() to register my own matching/searching function.  As SQLitening doesn't currently encapsulate sqlite3_create_function(), is it possible to call it directly on the client end, or will I need to add a server side proc to call it and access it via slRunProc?

Anyone know if it's possible to register a PB function with sqlite3_create_function()?

Bern Ertl

Nevermind... It appears that I can use the built in LIKE pattern matching to get what I need.