Index of substring in SQLite3?

Question

What's the most straightforward way of finidng the index of a substring in a varchar column? charindex doesn't exist in the stock version of SQLite3 -- which is still a little surprising to me.

Specifically, I have a column with values like 010000, 011000, 010110, etc. I want to find the index of the first occurence of 11. For the examples I gave, I would expect something like NULL (or -1), 1, and 3.

I have a hacked together idea that uses length and ltrim, but it seems like a lot of work for something I need to do several times.

Answer 1

This is now possible using the builtin instr function.

sqlite> select instr(010000,11);
0
sqlite> select instr(011000,11);
1
sqlite> select instr(010110,11);
3

Answer 2

Unfortunately, I think you've found the only answer that currently works. SQLite does not have a charindex equivalent function. You an make your own with length and trim, but nothing is built in:(