Regular expressions in SQL select/update for SQlite
Has any one been able to get regular expressions to work in either the command line SQLite tools or a GUI utility?
I've used the REGEXP statement successfully years ago for conditional tests - see Search & Replace.
I've not yet tried the re extension which adds:
- regexp_like(source, pattern)
- regexp_substr(source, pattern)
- regexp_replace(source, pattern, replacement)
That last one could be very helpful.
I just discovered that SQLiteSpy 1.9.15 supports the regexp_replace function!
regexp_replace('abcde','[bd]','_') a_c_e
but not regexp_like() or regexp_substr().
I wonder how long ago that was added. It's not listed explicitly in the software History.
That's the best news I could hear. I trying SQLspy now.
Tried SQLite Expert Personal 64, regexp is not supported but extensions are easily loaded. Downloaded sqlean-win-x64.zip from https://github.com/nalgeon/sqlean and extracted all to a folder. Loaded re.dll and all the functions work but I got different results from SQLiteSpy with regexp_replace() and that's troubling.
regexp_replace('abcde','[bd]','_')
a_cde
Spy gives the result I expected.
YES!
SQLite Expert is my preferred GUI and the sqlean "re" extension works as you say.
So far, I've tested regexp_like and regexp_replace from release 0.16.0 using the re.dll extension in sqlean-win-x64.zip and it works.
I'm so happy because I've been looking for this since I startled using RM a couple of years ago. This is going to save me so much manual editing as I adjust formatting of RM source names, etc.
It also looks like the extension tool set is being actively developed. Great news.
There is an associated web site that may be easier to navigate-
Yes, the sqlean extension function regexp_replace seems to stop after the first substitution is made in a string. I'll look at the code (maybe next week) and see if there are any undocumented flags that let it continue.
I have also come across an unexpected result with the sqlean extension function regexp_replace.
It works fine on columns that don't use RMNOCASE collation; but with columns using RMNOCASE, it generates an alarming error message "database disk image is malformed". The database is fine according to
pragma integrity_check.
I was able to work around this by running a "reindex" sql command before making any updates to the RMNOCAE collated columns. (I guess "reindex RMNOCASE" will be faster for big databases)
When I open the modified database in RM, I do a reindex with RM tools right away.
Does that sound right with you?
I was using unifuuz64. I think RMNOCASE_fake-SQLiteSpy64 behaved the same way.
First do you reindex after initiating unifuuz64? (before doing anything else)
Second have your tried Db Browser(SQLite)?
Quote from Richard Otter on 2022-09-08, 5:14 pmYes, the sqlean extension function regexp_replace seems to stop after the first substitution is made in a string. I'll look at the code (maybe next week) and see if there are any undocumented flags that let it continue.
In this post, the developer said:
The current version of
regex_replacereplaces only the first occurrence, so thea_cderesult is expected.I might change this behavior in one of the future versions for consistency with the built-in
replace.
He may need some encouragement...
Quote from Richard Otter on 2022-09-08, 5:14 pm... with columns using RMNOCASE, it generates an alarming error message "database disk image is malformed".
Certainly a misleading error message. Reindexing the database with a fake RMNOCASE or overriding in the SELECT statement the Column's collation sequence are the only ways to get around it. And, yes, on return to RM, run the database tool Rebuild Indexes which includes a SQLite REINDEX with the availability of the true RMNOCASE collation sequence. That is good practice with any SQL query of the RM database, especially those that modify data.