Regular expressions in SQL select/update for SQlite
I'm no expert on regular expressions but I now realise that sqlean re syntax is not even as complete as the POSIX Basic Regular Extension (BRE) syntax. If you are accustomed to the latter, be aware there are some pitfalls and frustrations using sqlean re. That said, it can still be mighty useful, especially the regexp_replace function.
sqlean re syntax
X* zero or more occurrences of X
X+ one or more occurrences of X
X? zero or one occurrences of X
(X) match X
X|Y X or Y
^X X occurring at the beginning of the string
X$ X occurring at the end of the string
. Match any single character
\c Character c where c is one of \{}()[]|*+?.
\c C-language escapes for c in afnrtv. ex: \t or \n
[abc] Any single character from the set abc
[^abc] Any single character not in the set abc
[a-z] Any single character in the range a-z
[^a-z] Any single character not in the range a-zOne that is missing is {m} for matching the preceding element exactly m times
Take this example from sqlean:
sqlite> select true where 'the year is 2021' regexp '[0-9]+';
1
But that would be true for a string of digits of any length anywhere in the subject. What if you want to test for the 4-digit string starting at the 13th character from the start of the line?
In POSIX BRE (and SQliteSpy), this would work:
select true where 'the year is 2021' regexp '^.{12}[0-9]{4}.*';
Using sqlean re, it has to be:
select true where 'the year is 2021' regexp '^............[0-9][0-9][0-9][0-9].*'
Thanks for the summary.
Regular expressions are implemented in so many different ways, I'm not at all surprised about the differences.
Quote from Tom Holden on 2022-09-07, 10:12 pmTried 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.
Try using "regexp.dll" from the same source. It works for me.
Thanks, Ed. He renamed it since I downloaded the bundle.
I have been using regular expressions for years in various contexts, but never before with SQLite and RM. And by the way, I concur that no two dialects of regular expressions are ever the same.
In any case, I just now created my first SQLite script for RM with regular expressions. It's pretty simple and it's very specific to my database. But I thought I would share it anyway as an example of what can be done.I don't expect it to be usable as is by anybody else. It's just for ideas.
I'm still just using SQLiteSpy, and my version seems to have come with regular expression support built in. It's possible I added the regular expression extension and that I have forgotten doing so, but I don't think so. In any case, the installed extension only supports the REGEXP operator, the regexp function, and the regexp_replace function. It doesn't support regexp_instr or regexp_substr or any other functions. But I was able to get by with regexp_replace.
My use case is that I have lots of census notes where I have transcribed the person's birth places as TN NC VA and where I want to change all such transcriptions in my notes to born TN, father born NC, mother born VA . I am not changing the transcriptions on my Web site nor in my Research Notes in RM. This is just for the fact notes that appear in reports.
Uploaded files: