Combining substr and instr to extract text#

Derek Willis has a Datasette instance full of political campaign emails running on Heroku.

Matt Hodges pointed out that a lot of these emails include refcode= codes, which are used by ActBlue campaigns to track clicks.

They look like this:

...c-email?refcode=220210_FR_midmonth1kavin_plain] Jessica Mason...
...hmp-footer?refcode=2021_footer&amount=25&a...

I thought it would be fun to extract just the codes.

The datasette-rure plugin adds regular expression support which can be used for this kind of thing, but in the absence of a plugin like that the only way to do it is with the SQLite instr() and substr() functions.

Here’s the query I figured out:

1
with snippets as (
2
  select
3
    substr(body, instr(body, 'refcode=') + 8, 128) as snippet
4
  from
5
    emails
6
  where
7
    body LIKE '%refcode%'
8
),
9
refcodes as (
10
  select
11
    snippet,
12
    substr(
13
      snippet,
14
      0,
15
      min(
16
        case
17
          when instr(snippet, '&') > 0 then instr(snippet, '&')
18
          else 128
19
        end,
20
        case
21
          when instr(snippet, ']') > 0 then instr(snippet, ']')
22
          else 128
23
        end,
24
        case
25
          when instr(snippet, ' ') > 0 then instr(snippet, ' ')
26
          else 128
27
        end,
28
        case
29
          when instr(snippet, '.') > 0 then instr(snippet, '.')
30
          else 128
31
        end
32
      )
33
    ) as refcode
34
  from
35
    snippets
36
)
37
select
38
  refcode,
39
  count(*) as n
40
from
41
  refcodes
42
group by
43
  refcode
44
order by
45
  n desc

I started by pulling out just the 128 characters following each refcode= - I picked 128 characters at random just to make the data easier to look at:

1
    substr(body, instr(body, 'refcode=') + 8, 128) as snippet

instr(body, 'refcode=') + 8 gives the character after the = sign, because refcode= is 8 characters long.

Next I needed to find the first character following the refcode that was either a &, a ], a space or a.`. That’s what this bit does:

1
    substr(
2
      snippet,
3
      0,
4
      min(
5
        case
6
          when instr(snippet, '&') > 0 then instr(snippet, '&')
7
          else 128
8
        end,
9
        case
10
          when instr(snippet, ']') > 0 then instr(snippet, ']')
11
          else 128
12
        end,
13
        case
14
          when instr(snippet, ' ') > 0 then instr(snippet, ' ')
15
          else 128
16
        end,
17
        case
18
          when instr(snippet, '.') > 0 then instr(snippet, '.')
19
          else 128
20
        end
21
      )
22
    ) as refcode

I’m trying to find the first instance of any of those characters - so I use instr to find them, but ignore any results where that returns 0 for “character not found” - in those cases I use the number 128 picked earlier. I can then grab the minimum of those scores.

Then finally I do a group-by/count to find the most common refcodes:

1
select
2
  refcode,
3
  count(*) as n
4
from
5
  refcodes
6
group by
7
  refcode
8
order by
9
  n desc

refcode	n
email_footer	527
em_pt	352
em_fr_2020	285
pt	254
emfooter	242
em_fr_2021	242
footer-bio	198
footer_button	192
em_footer	173
email-footer	168
	168
footer	164
em2021	135
em_jc_fr_footer_link	107
em_fr_2019	107
em-footer	104
em_fr_2018	84
ABD_EM_FR_2021	67