REGEXP and SQLITE

[Fangbeast]

I am trying to apply REGEXP to SQLITE3.x searches but never having used them before, I am not getting where I thought it would.


In the database, I would have the following author names:


Edward E. Smith, Jack Gaughan
Edward E. Smith, A. J. Donnell
Edward E. Smith


When a user highlights a record that might contain:


"Edward E. Smith, Jack Gaughan"


How would they get all matches with "Edward E. Smith" in them? Is it possible using REGEXP?

[Tommy]

SELECT * FROM tblAuthors WHERE author_name LIKE "Edward E. Smith%"

[Fangbeast]

SELECT * FROM tblAuthors WHERE author_name LIKE "Edward E. Smith%"

Thanks Tommy, but you didn't read my post above.

The author field may contain more than one author on that line I.e

Edward E. Smith, Jack Gaughan
Edward E. Smith, A. J. Donnell
Edward E. Smith

So when a user selects the function to get more titles for that author, he would possibly select a line that contains:

Edward E. Smith, Jack Gaughan

so the query would be:

"Select * From library Where author Like '%Edward E. Smith, Jack Gaughan%';

and I would only get back:

Edward E. Smith, Jack Gaughan

which is a full match.

I need to tailor a query somehow that it returns all matches for the primary author Edward E. Smith and woult return all matches with his name in it.

so if I input "Edward E. Smith, Jack Gaughan", is there a way of segregating the primary author name Edward E. Smith and returning all matches with his name in it? Your query is an exact substring match.

Edward E. Smith, Jack Gaughan
Edward E. Smith, A. J. Donnell
Edward E. Smith

[Thalius]

"Like" in SQL is a partial Match.

As a hint if your Database is big don't use * in your SQL Syntax .. specify the tables you want to use ( is faster ) and if you don't need an endless Result or have a max of Results needed anyway i woudl add "Limit xx" to it ( Limiting results also is a speedup ).

For regExp you may wanna check this:
http://www.phpbuilder.com/columns/dario19990616.php3

Thalius

[Fangbeast]

"Like" in SQL is a partial Match.

As a hint if your Database is big don't use * in your SQL Syntax .. specify the tables you want to use ( is faster ) and if you don't need an endless Result or have a max of Results needed anyway i woudl add "Limit xx" to it ( Limiting results also is a speedup ).

For regExp you may wanna check this:
http://www.phpbuilder.com/columns/dario19990616.php3

Thalius

All good points and I am aware of them already thanks. REGEXP is what I need to learn and have it bashed into my brain

[Dare2]

I didn't realise you could use REGEXP in SQL syntax. Can you give a link to something that covers this?

I know that this is not what you are after, but the easiest way seems to be to build a simple WHERE construct:

(Replace the "fieldname =" with "fieldname LIKE" if the fieldname has multiple authors)

Code: Select all

auth.s = "A. Whopper,B. Kalm"

mySelect.s = "SELECT * FROM TableName WHERE "
For i = 1 To CountString(auth,",")+1
  If StringField(auth,i,",") <> ""
    mySelect + "fieldName = "+Chr(34)+StringField(auth,i,",")+Chr(34)+" OR "
  EndIf
Next
mySelect = Left(mySelect,Len(mySelect)-4)

Debug mySelect

*ducks*

Did a web search for REGEXP and SQLITE3 and didn't find anything I understood or pertinent so a link would be appreciated.

[Fangbeast]

Uncle Traumaticus pointed out to me (In the irc channel) that REGEXP had nothing to do with Sqlite so I went hunting in the MySql 5.1 CHM manual and read up on the sucker.

Well twist my nipple nuts and flap me sideways to Alaska!! It made about as much sense and flopping your love spuds into a lion's mouth and flicking his love jewels with a wet towel!!

But what do you know, it started to make a little sense and it now doesn't hurts as much as shaving my scrotum with a blunt razor.

It's actually making a little sense (Oh, I will pay for that, my brain doesn't understand that word)

[traumatic]

Uncle Traumaticus pointed out to me (In the irc channel) that REGEXP had nothing to do with Sqlite so I went hunting in the MySql 5.1 CHM manual and read up on the sucker.

Dear cousin Fanglongmon,
to be honest, back then I didn't know what SQlite actually is.

Now I understand it's the DB itself, not just an engine to gain access to
various DBs - which I thought it was.

Anyway, as SQlite implements most of SQL92 (REGEXP is part of that)
using the MySQL manual isn't the worst idea (even though MySQL
doesn't conform to SQL in all concerns). *pheew*

Regular expressions are really powerful, not just in terms of DB applications
- it will never be wrong if you know how to use them - yet I don't understand
why building a simple query like "SELECT whatever FROM table WHERE field
LIKE '%item1%' OR field LIKE '%item2%' [...]" (or even without using LIKE,
see Dare2's post) doesn't help with your problem.

[Dare2]

Traumaticus? Fanglongmon?

LOLium primus!

[Fangbeast]

Dare2, if you like the above, you should see what we sometimes get up to on IRC. Especially when Bericko isn't around to log us (very evil grin)

[Fangbeast]

Uncle Traumaticus pointed out to me (In the irc channel) that REGEXP had nothing to do with Sqlite so I went hunting in the MySql 5.1 CHM manual and read up on the sucker.

Dear cousin Fanglongmon,
to be honest, back then I didn't know what SQlite actually is.

Now I understand it's the DB itself, not just an engine to gain access to
various DBs - which I thought it was.

Anyway, as SQlite implements most of SQL92 (REGEXP is part of that)
using the MySQL manual isn't the worst idea (even though MySQL
doesn't conform to SQL in all concerns). *pheew*

Regular expressions are really powerful, not just in terms of DB applications
- it will never be wrong if you know how to use them - yet I don't understand
why building a simple query like "SELECT whatever FROM table WHERE field
LIKE '%item1%' OR field LIKE '%item2%' [...]" (or even without using LIKE,
see Dare2's post) doesn't help with your problem.

Dear cousing Traumaticus, this is your long lost (and exceedingly poor but extremely ugly) cousin Fanglomon speaking. It's sad to say that people really don't understand my post as I have been speaking in Auslisch or they would understand why it doesn't work.

Since everyone is already re-stating the bleedin' obvious (old Englisch slang here, not Auslisch) I'd like to say again that although I am an extremely ugly, ingratiating, inbred SOB with no brains, skills or talent to speak of and am related to a distant branch of the family (so distant that your mother never speaks of it, she forgot!!) that I already know everyone's hints above and knew then 3 years ago and they don't work for what I was trying to do.

If the size of my brain was as big as my flappy lips, I could probably explain it better to my genius cousin Traumaticus so that he could follow my feeble explanation but since i want to live past 90 and it would take that long for me to develop some useful braincells (And my brain size is exceeded by the size of my feet) to explain it all, it's far easier if I just go around the problem.

And I did.

Go around the problem!.

After all, I don't want to sit here for years explaining it.

I'll get uglier!!

I'll get bigger feet!!

(/me slowly slinks into the sunset, howling like a lunatic and waits for the knock on the door.."Knock Knock", "yes??. "nooooo, Dare2, what are you doing here....No, put down cousing Traumaticus, don't beat me with his slime encsrusted body!!, what are you doing with that cat?? ARRRGHGHGHGHGHHGHG")

[traumatic]

Cousin Fangle'o'maniac,

I'm sorry to say but I can't let you go with your lame excuse, mummy
would just never forgive me (us, them, everybody, everybody).

Lack of brain is nothing to be ashamed of. After all we're all the same familiy!

That having said, please (pleeeeeeaaaaase) bring yourself to explain your
actual problem again - I'm sure it's worth it. REGEXP is especially useful if
you have problems like

"I need to find every item that contains three of the same letters in a row,
followed by a positive number, starting with an 'F'"

"Show me everything between <Fang> and </les> - but I only need
capital letters"

etc.

As said on IRC, I don't think REGEXP will help you in your special case.
Just explain it again, we'll find a solution.

Please
Please
Please
Please


BTW: Size doesn't matter!