Strange return from sqlean regexp

[han-holl]

The following:
#!/bin/ruby
require 'sequel'

DB = Sequel.sqlite('/data/abn/abn.db',
after_connect: proc do|db|
db.enable_load_extension(true)
db.load_extension('./sqlite/regexp.so')
end
)

Items = DB[:main]
pp Items.select{regexp_substr('the year is 2021', '[0-9]+')}.first

=== end of program

returns:
{:"regexp_substr('the year is 2021', '[0-9]+')"=>"2021"}

The SQL is 👍
Sequel::SQLite::Dataset: "SELECT regexp_substr('the year is 2021', '[0-9]+') FROM main"

If I run that in the Sqlite CLI, I get
2021

Han Holl

(I wasn't quite sure if this is a new discussion)
(And how do I get the nice syntax coloring?)

[jeremyevans]

That result is what I expect. Dataset#first returns a hash for the first row in the dataset. If you only want a single value, use get instead of first. The reason for the key name is that's the default key name SQLite uses for your expression if you don't give it an explicit alias.

[han-holl]

Thank you.
I had no luck with get.
pp Items.select{regexp_substr('the year is 2021', '[0-9]+')}.get
returned nil
pp Items.select{regexp_substr('the year is 2021', '[0-9]+')}.first.values
got me: ["2021"]

Should I give an argument to get ? I looked at the get docs, but nothing suggested itself.
I tried:
pp Items.select{Sequel.as(regexp_substr('the year is 2021', '[0-9]+'), :jaar)}.get(:jaar)
but got:
SQLite3::SQLException: no such column: jaar:

Han Holl

[jeremyevans]

Sorry, the example I gave was bad. You can use get to replace both select and first:

Items.get{regexp_substr('the year is 2021', '[0-9]+')}

[han-holl]

Yes, works just fine.
Thank you for your patience.

Cheers,

Han Holl