Geek Answers Handbook

Substring index in SQLite3?

What is the easiest way to determine the substring index in a varchar column? charindex does not exist in the original version of SQLite3, which is still a bit surprising for me.

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

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

+7
string substring sqlite3
source share
2 answers

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

+2
source share

This is now possible with the built-in instr function.

 sqlite> select instr(010000,11); 0 sqlite> select instr(011000,11); 1 sqlite> select instr(010110,11); 3
+5
source share

More articles:


All Articles