| 8111 |
A CSV export, JSON import workflow for bulk updating our data |
with extracted_idrefs as (
select
public_id, name,
(regexp_match(name, 'Walgreens.*#0*([0-9]{1,8})', 'i')) [1] as idref
from
location
where
regexp_match(name, 'Walgreens.*#0*([0-9]{1,8})', 'i') is not null
)
select
json_build_object(
'update',
json_object_agg(
public_id,
json_build_object('add', json_build_array('walgreens:' || idref), 'comment', name)
)
)
from
extracted_idrefs
where
public_id not in (
select
public_id
from
location
where
id in (
select
location_id
from
concordance_location
where
concordanceidentifier_id in (
select
id
from
concordance_identifier
where
authority = 'walgreens'
)
)
) |
https://simonwillison.net/e/8111 |
| 8084 |
Django SQL Dashboard |
with results_stripped as (
select id, title,
replace(replace(replace(replace(replace(regexp_replace(
(regexp_matches(body, '<pre>(.*?)</pre>', 'g'))[1],
E'<[^>]+>', '', 'gi'
), '"' || chr(59), '"'), '>' || chr(59), '>'), '<' || chr(59), '<'), ''' || chr(59), chr(39)), '&' || chr(59), '&'
) as code
from
blog_entry
where
body ~ '<pre>.*<pre>'
)
select id, title, code, 'https://simonwillison.net/e/' || id as link from results_stripped
where code like '%%' || %(search)s || '%%' limit 10 |
https://simonwillison.net/e/8084 |
| 8084 |
Django SQL Dashboard |
regexp_matches(body, '<pre>(.*?)</pre>', 'g'))[1]
|
https://simonwillison.net/e/8084 |
| 7985 |
Personal Data Warehouses: Reclaiming Your Data |
select
created_at,
regexp_match('.*?(\d+(\.\d+))lb.*', full_text, 1) as lbs,
full_text,
case
when (media_url_https is not null)
then json_object('img_src', media_url_https, 'width', 300)
end as photo
from
tweets
left join media_tweets on tweets.id = media_tweets.tweets_id
left join media on media.id = media_tweets.media_id
where
full_text like '%lb%'
and user = 3166449535
and lbs is not null
group by
tweets.id
order by
created_at desc
limit
101 |
https://simonwillison.net/e/7985 |
| 7909 |
Weeknotes: ONA19, twitter-to-sqlite, datasette-rure |
select regexp_match('.*( and .*)', title) as n from articles where n is not null
-- Returns the ' and X' component of any matching titles, e.g.
-- and Recognition
-- and Transitions Their Place
-- etc
select regexp_matches(
'hello (?P<name>\w+) the (?P<species>\w+)',
'hello bob the dog, hello maggie the cat, hello tarquin the otter'
)
-- Returns a JSON array:
-- [{"name": "bob", "species": "dog"},
-- {"name": "maggie", "species": "cat"},
-- {"name": "tarquin", "species": "otter"}]
|
https://simonwillison.net/e/7909 |