31:[{"id":"2b031e785dc924940c34df090e51a5671554390bfb415959d2388b3bf0dd25742a85edbeb7df5b49dd598ca863b58c30f081c2c984d1a5c696a3eb75396378cb","href":"https://static.observableusercontent.com/files/2b031e785dc924940c34df090e51a5671554390bfb415959d2388b3bf0dd25742a85edbeb7df5b49dd598ca863b58c30f081c2c984d1a5c696a3eb75396378cb","name":"1.jpg","type":"image/jpeg","lastModified":1674073147702.628,"size":60156},{"id":"1ab42688bbd8703204293de432c0704c98998a1dce900becdef041f600155d9eb2cf38314aa408130ed3f73d8a68eb08adc5d4841c4a4dac0e56123054259725","href":"https://static.observableusercontent.com/files/1ab42688bbd8703204293de432c0704c98998a1dce900becdef041f600155d9eb2cf38314aa408130ed3f73d8a68eb08adc5d4841c4a4dac0e56123054259725","name":"results.json","type":"application/json","lastModified":1674089366150.589,"size":3145},{"id":"9a0e7155fb5bb34ecef74f1e73b88ba44cf98160cf58711c7b24a3b30f24137e440d5d0a8417e7135e1ac2f0a2ffac14b4a6f91d23b3f8f6c8cf8745ea0fd5d5","href":"https://static.observableusercontent.com/files/9a0e7155fb5bb34ecef74f1e73b88ba44cf98160cf58711c7b24a3b30f24137e440d5d0a8417e7135e1ac2f0a2ffac14b4a6f91d23b3f8f6c8cf8745ea0fd5d5","name":"3.jpg","type":"image/jpeg","lastModified":1674073147727.368,"size":53366},{"id":"3a2c002b47852b5506b466e0f49876afadc40d7afba39188b49967f9934c2cc769adfe54be6adab7bad221eb17206af1552d6a682b270c8bde3c30d24dafaee2","href":"https://static.observableusercontent.com/files/3a2c002b47852b5506b466e0f49876afadc40d7afba39188b49967f9934c2cc769adfe54be6adab7bad221eb17206af1552d6a682b270c8bde3c30d24dafaee2","name":"2.jpg","type":"image/jpeg","lastModified":1674073147993.15,"size":65197}] 2f:{"title":"Introducing sqlite-regex: A fast Regular Expression Extension for SQLite","cells":[{"id":0,"value":"# Introducing sqlite-regex: A fast Regular Expression Extension for SQLite","pinned":false,"hidden":false,"mode":"md"},{"id":8,"value":"[sqlite-regex](https://github.com/asg017/sqlite-regex) is a new SQLite extensions for using Regular Expressions in SQL. Written in Rust, based on [sqlite-loadable](https://github.com/asg017/sqlite-loadable-rs) and the excellent [regex crate](https://docs.rs/regex/latest/regex/)!\n","pinned":false,"hidden":false,"mode":"md"},{"id":39,"value":"

\n ${(await Promise.all([\n FileAttachment(\"1.jpg\").url(),\n FileAttachment(\"2.jpg\").url(),\n FileAttachment(\"3.jpg\").url()\n])).map(d=>htl.html`

`)}\n

\n DALL·E attempts for this post's thumbnail\n ","pinned":false,"hidden":false,"mode":"html"},{"id":17,"value":"## How `REGEXP()` works in SQLite\n\nUnlike most other database engines, SQLite doesn't distribute a regular expression implementation by default. There is a [`REGEXP()`](https://www.sqlite.org/lang_expr.html#the_like_glob_regexp_match_and_extract_operators) operator in the SQLite language, but you need to define a SQL function called \"regexp\" in order to use it. \n\n```sql\nselect 'foobar' regexp 'foo'; -- \"Error: no such function: regexp\"\n\n.load ./regex0\nselect 'foobar' regexp 'foo'; -- 1\n\n```\n\n\nThe SQLite team does offer a [regexp.c](https://github.com/sqlite/sqlite/blob/master/ext/misc/regexp.c) extension that you can use to provide a `REGEXP()` function, but requires compiling yourself. It is included in the SQLite CLI starting in version `3.36.0` (2021-06-18), but it still lacks many features that other regular expression engines include. \n\nSo, `sqlite-regex` offers an alternative `REGEX()` implementation, with a modern regex engine and several other regex utilities!","pinned":false,"hidden":false,"mode":"md"},{"id":25,"value":"## Speed\n\nLike other SQLite regular expression extensions, `sqlite-regex` uses SQLite's [`sqlite3_get_auxdata()`/`sqlite3_set_auxdata()`](https://www.sqlite.org/c3ref/get_auxdata.html) APIs to avoid re-compiling regular expressions for every row of a query. This, along with the absurd performance benefits from the underlying [Rust regex crate](https://crates.io/crates/regex), means that `sqlite-regex` is ~2x faster than the official SQLite regexp extension, and 6x faster than sqlean's re extension!","pinned":false,"hidden":false,"mode":"md"},{"id":167,"value":"viewof replay = Inputs.button(\"Replay\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":169,"value":"bench = (replay,\nimportCell(\"chart\", \"@asg017/hyperfine-benchmark-viz1\", {\n animated: true,\n highlight: [\"./sqlite-regex.sh\"],\n compareTo: \"./sqlite-regex.sh\",\n title: \"Running REGEXP() on 200k rows\",\n footer: \"sqlite-regex v0.1.0 on a 2019 Macbook\",\n data: await FileAttachment(\"results.json\").json()\n}))","pinned":false,"hidden":false,"mode":"ojs"},{"id":180,"value":"See [the Benchmarks page](https://github.com/asg017/sqlite-regex/tree/main/benchmarks) for details.","pinned":false,"hidden":false,"mode":"md"},{"id":196,"value":"## Try it yourself\n\n","pinned":false,"hidden":false,"mode":"md"},{"id":198,"value":"Here I have a [Datasette instance](https://datasette.io/) that has the `sqlite-regex` extension loaded. It's attached to this Observable notebook with [Datasette Client](https://observablehq.com/@ambassadors/datasette-client), so feel free to edit the SQL cells below to try it out!","pinned":false,"hidden":false,"mode":"md"},{"id":97,"value":"select \n regex_version(),\n regex_debug(),\n 'foobar' regexp 'foo';","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":10,"value":"## Extra `sqlite-regex` features\n\nIn addition to many performance and usability benefits over the [official REGEXP extensions](https://sqlite.org/src/file/ext/misc/regexp.c) and the [sqlean](https://github.com/nalgeon/sqlean) project's [re](/blob/main/docs/re.md) extension, there are a few other regex features that `sqlite-regex` offers, such as:","pinned":false,"hidden":false,"mode":"md"},{"id":36,"value":"### RegexSet support\n\n[`RegexSet`](https://docs.rs/regex/latest/regex/struct.RegexSet.html) offer a fast way to match multiple regular expressions onto a string in a single scan. `sqlite-regex` supports regexsets with the [`regexset()`](https://github.com/asg017/sqlite-regex/blob/main/docs.md#regexset) and \n[`regexset_is_match()`](https://github.com/asg017/sqlite-regex/blob/main/docs.md#regexset_is_match) functions like so:\n","pinned":false,"hidden":false,"mode":"md"},{"id":121,"value":"select regexset_is_match(\n regexset(\n \"bar\",\n \"foo\",\n \"barfoo\"\n ),\n 'foobar'\n)","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":191,"value":"There's also the [`regexset_matches`](https://github.com/asg017/sqlite-regex/blob/main/docs.md#regexset_matches) table function for finding all the matching regular expressions for given text, which can be joined onto a table of data:","pinned":false,"hidden":false,"mode":"md"},{"id":102,"value":"with test_strings as (\n select value as string\n from json_each('[\n \"example.com\",\n \"foo@example.com\",\n \"example\"\n ]')\n)\nselect \n test_strings.string,\n matches.pattern\nfrom test_strings\nleft join regexset_matches(\n regexset(\n '[a-z]+@[a-z]+\\.(com|org|net)',\n '[a-z]+\\.(com|org|net)'\n ),\n test_strings.string\n) as matches","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":15,"value":"### Get occurrences of a pattern in a string\n\nUse the [`regex_find()`](https://github.com/asg017/sqlite-regex/blob/main/docs.md#regex_find) function to extract the resulting pattern from a string, or the \n[`regex_find_all()`](https://github.com/asg017/sqlite-regex/blob/main/docs.md#regex_find_all) table function to find all occurrences of a pattern in a string.","pinned":false,"hidden":false,"mode":"md"},{"id":125,"value":"select regex_find(\n '\\b\\w{13}\\b', \n 'I categorically deny having triskaidekaphobia.'\n) as word_13_chars;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":138,"value":"select *\nfrom regex_find_all(\n '\\b\\w{13}\\b', \n 'Retroactively relinquishing remunerations is reprehensible.'\n);","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":31,"value":"### Split strings on a regex pattern\n\nUse the [`regex_split()`](https://github.com/asg017/sqlite-regex/blob/main/docs.md#regex_split) table function to split apart tricky string into multiple rows!","pinned":false,"hidden":false,"mode":"md"},{"id":130,"value":"select * \nfrom regex_split(\n regex('[ \\\\t]+'), \n 'a b \\t c\\td e'\n)","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":33,"value":"### Replace occurrences of a pattern in a string\n\nUse [`regex_replace()`](https://github.com/asg017/sqlite-regex/blob/main/docs.md#regex_replace) and [`regex_replace_all()`](https://github.com/asg017/sqlite-regex/blob/main/docs.md#regex_replace_all) to replace occurences of a pattern in a string! The Rust regex crate also offers a nifty [replacement string syntax](https://docs.rs/regex/latest/regex/struct.Regex.html#replacement-string-syntax) where you can reference capture groups in your replacement.","pinned":false,"hidden":false,"mode":"md"},{"id":134,"value":"select \n regex_replace(\n '[^01]+', \n '1078910', \n ''\n ) as only_binary,\n regex_replace(\n '(?P[^,\\s]+),\\s+(?P\\S+)', \n 'Springsteen, Bruce', \n '$first $last'\n ) as first_last","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":132,"value":"select regex_replace_all(\n 'dog',\n 'cat dog mouse dog',\n 'monkey'\n)","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:db"},{"id":5,"value":"## Stay in the loop!\n\nThis is a post in my [SQLite blog series](https://observablehq.com/@asg017/a-new-sqlite-blog-series), where I talk about new tools and SQLite extensions that I am making, guides, and how-to's. Consider subscribing to the [email newsletter](https://buttondown.email/alexgarcia)! **No more than one (1) post a week**.\n\n\n\nAlternatively, there is an [RSS feed](https://buttondown.email/alexgarcia/rss), or you can [follow me on Twitter](https://twitter.com/agarcia_me) or [Mastodon](https://data-folks.masto.host/@alexgarciaxyz).\n","pinned":false,"hidden":false,"mode":"md"},{"id":75,"value":"---","pinned":false,"hidden":false,"mode":"md"},{"id":77,"value":"import { DatasetteClient } from \"@ambassadors/datasette-client\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":83,"value":"db = new DatasetteClient(\"https://sqlite-extension-examples.fly.dev/data\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":171,"value":"import { importCell } from \"@mbostock/dataflow\"","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W31","stdlib":"1","titling":"auto"}