REGEXP and SQLITE

Question

Anyone have a suggestion how I might be able to write a REGEXP in SQLITE that uses the same functionality as \K in say Teradata.

Example:

text = "height"=>"5","xxxxxxxxxxx"

Regexp:

SELECT * FROM xxxxx where xxxxx = 'xxxxxxx'and xxxxxx 
where REGEXP '"height".+?"\K\d+'

This returns the integer 5.

This regepx works fine in Teradata but not SQLITE. I am using DB Browser (SQLite)

Thanks in advance.

Sqlite doesn't have built-in support for regular expressions. You have to use an extension module that provides the syntax you need. (I have a few I'm working on for different RE engines, but nothing really production ready yet). — Shawn, Apr 07 '20 at 06:20
Thanks Shawn. I use DB Browser (SQLite) which allows me to run REGEXP. — Banana, Apr 07 '20 at 06:21
Perhaps use a capturing group `"height".+?"(\d+)` https://regex101.com/r/rihFjd/1 — The fourth bird, Apr 07 '20 at 06:31
Check https://stackoverflow.com/questions/30643061/sqlite-db-browser-invalid-operand-for-regexp — Wiktor Stribiżew, Apr 07 '20 at 09:02

score 0 · Answer 1 · edited Jul 22 '20 at 10:58

0

Got it sorted, instead of a regexp my boss (smart cookie) suggested I do the following:

Update XXXXX
Set height = Case when instr(XXXXXXX,'"height"=>"') > 0
then substr(XXXXXXX,instr(XXXXXXXs,'"height"=>"')+11,instr(substr(XXXXXXX,instr(XXXXXXX,'"height"=>"')+11,5),'"')-1)
else ''
end

edited Jul 22 '20 at 10:58

Wiktor Stribiżew

607,720
39
448
563

answered Apr 10 '20 at 22:34

Banana

11
2

REGEXP and SQLITE

1 Answers1