Keep substring that precedes an expression in SQLite

Question

I want to split a varchar column on a certain expression and keep the left hand side of the result.

My column looks as follows:

varchar_col
keep_this__discard_this
keep_this_too__discard_this

I want to split all the strings on the double underscore ('__') and keep whatever comes before it. How can this be done in SQLite?

Answer 1

You can use:

select substr(varchar_col, 1, instr(varchar_col, '__') - 1)

Here is a db<>fiddle.