How to replace instr() function in SQLite?

Question

Given that instr() function isn't supported in SQLite KitKat Android how can I obtain following expression in a SQL statement?

substr(mystring, instr(mystring, '_')+1)

My target is to get first part of a string until '_' where its position is variable. I use this expression in a JOIN clause and I need it directly in SQL.

Thanks in advance. Danilo

"Given that instr() function isn't supported in SQLite KitKat" source? This https://www.sqlite.org/lang_corefunc.html#instr says instr is in sqlite, and I haven't found any indication that kitkat would not have it — njzk2, Feb 20 '18 at 07:10
@njzk2 added **2012-12-12 (3.7.15)** as per [http://www.sqlite.org/changes.html#version_3_7_15](http://www.sqlite.org/changes.html#version_3_7_15) — MikeT, Feb 20 '18 at 07:13
@MikeT Thanks. Together with https://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android it confirms that kitkat does not have `instr` function — njzk2, Feb 21 '18 at 02:37

MikeT · Answer 1 · 2018-02-21T20:38:43.687

Perhaps not the best/ideal/mot elegant way, but you could use something based upon the following :-

-- Create testing Table and data
--CREATE TABLE IF NOT EXISTS noinstr (test TEXT);
--INSERT INTO noinstr VALUES('short_xxx');
--INSERT INTO noinstr VALUES('thisisevenlonger_yyyyyyyy');
--INSERT INTO noinstr VALUES('nothing to return in this text');
SELECT 
CASE 
    WHEN substr(test,2,1) = '_' THEN substr(test,1,1)
    WHEN substr(test,3,1) = '_' THEN substr(test,1,2)
    WHEN substr(test,4,1) = '_' THEN substr(test,1,3)
    WHEN substr(test,5,1) = '_' THEN substr(test,1,4)
    WHEN substr(test,6,1) = '_' THEN substr(test,1,5)
    WHEN substr(test,7,1) = '_' THEN substr(test,1,6)
    WHEN substr(test,8,1) = '_' THEN substr(test,1,7)
    WHEN substr(test,9,1) = '_' THEN substr(test,1,8)
    WHEN substr(test,10,1) = '_' THEN substr(test,1,9)
    WHEN substr(test,11,1) = '_' THEN substr(test,1,10)
    WHEN substr(test,12,1) = '_' THEN substr(test,1,11)
    WHEN substr(test,13,1) = '_' THEN substr(test,1,12)
    WHEN substr(test,14,1) = '_' THEN substr(test,1,13)
    WHEN substr(test,15,1) = '_' THEN substr(test,1,14)
    WHEN substr(test,16,1) = '_' THEN substr(test,1,15)
    WHEN substr(test,17,1) = '_' THEN substr(test,1,16)
    WHEN substr(test,18,1) = '_' THEN substr(test,1,17)
    --- More as required
    ELSE ''
END AS result
FROM noinstr

The initial commented out statements create the table noinstr with a single column name test and populate the table with 3 rows as per :-

The query returns :-

EDIT re comment :-

This "search and test" approach seem a little bit convoluted but it works. But what if string is too much long? You have to hard code the test for all possible character position

You could always generate the SQL based upon the maximum length e.g. :-

public String buildCaseClause(String coretable,
                              String corecolumn,
                              String mvcolumn,
                              String separator,
                              String resultcolumn ) {
    long maxstrlen = 0;

    // Get maximun length of Data
    Cursor mv = mDB.query(
            coretable,
            new String[]{"max(length(" + corecolumn + ")) AS " + mvcolumn},
            null,null,null,null,null
    );
    if(mv.moveToFirst()) {
        maxstrlen = mv.getLong(mv.getColumnIndex(mvcolumn));
    }
    mv.close();

    // If max string length is less than 2 then return an empty string
    if (maxstrlen < 1) {
        return "";
    }
    //WHEN substr(test,2,1) = '_' THEN substr(test,1,1)
    StringBuilder rv = new StringBuilder(" CASE");
    for (int i=2;i <= maxstrlen;i++) {
        rv.append(
                " WHEN substr(" +
                        corecolumn+
                        "," + String.valueOf(i) +
                ",1) = '" + separator +
                "' THEN substr(" +
                        corecolumn +
                        ",1," + String.valueOf(i-1) + ")");
    }
    // Add
    rv.append(" ELSE '' END AS " + resultcolumn);
    return rv.toString();
}

This could then be called like :-

public Cursor getSplitDataPart() {
    return mDB.query(TBNOINSTR,
            new String[]{"*",buildCaseClause(TBNOINSTR,COL_TBNOINSTR_DATA,"mv","_", "result")},
            null,null,null,null,null);
}

Which could itself be used like :-

    SO48879002DBHlpr dbhlpr = new SO48879002DBHlpr(this);
    // Add some testing data
    dbhlpr.addData("short_secondpart");
    dbhlpr.addData("thisislonger_secondpart");
    dbhlpr.addData("thisisanevenlongerfirstdatapart_secondaprt");
    // Log the generated SQL that could be incoporated
    Log.d("SPLIDATASQL",dbhlpr.buildCaseClause(SO48879002DBHlpr.TBNOINSTR,SO48879002DBHlpr.COL_TBNOINSTR_DATA,"mv","_","result"));
    // Extract the data into a cursor
    Cursor csr = dbhlpr.getSplitDataPart();
    // Log the results
    while (csr.moveToNext()) {
        Log.d("DBINFO","Extracted First Datapart is " + csr.getString(csr.getColumnIndex("result")));
    }

SO48879002DBHlpr being the DatabaseHelper (subclass of SQLiteOpenHelper)
the buildCaseClause could be private, however for testing it wa made public thus facilitating the use of Log.d("SPLIDATASQL",dbhlpr.buildCaseClause(SO48879002DBHlpr.TBNOINSTR,SO48879002DBHlpr.COL_TBNOINSTR_DATA,"mv","_","result")); for testing.
TB_NOINSTR is the table name , COl_TBNOINSTR_DATA is the co-joined data.

In this case the resultant log includes :-

02-21 20:14:01.623 1690-1690/? D/SPLIDATASQL:  CASE WHEN substr(_data,2,1) = '_' THEN substr(_data,1,1) WHEN substr(_data,3,1) = '_' THEN substr(_data,1,2) WHEN substr(_data,4,1) = '_' THEN substr(_data,1,3) WHEN substr(_data,5,1) = '_' THEN substr(_data,1,4) WHEN substr(_data,6,1) = '_' THEN substr(_data,1,5) WHEN substr(_data,7,1) = '_' THEN substr(_data,1,6) WHEN substr(_data,8,1) = '_' THEN substr(_data,1,7) WHEN substr(_data,9,1) = '_' THEN substr(_data,1,8) WHEN substr(_data,10,1) = '_' THEN substr(_data,1,9) WHEN substr(_data,11,1) = '_' THEN substr(_data,1,10) WHEN substr(_data,12,1) = '_' THEN substr(_data,1,11) WHEN substr(_data,13,1) = '_' THEN substr(_data,1,12) WHEN substr(_data,14,1) = '_' THEN substr(_data,1,13) WHEN substr(_data,15,1) = '_' THEN substr(_data,1,14) WHEN substr(_data,16,1) = '_' THEN substr(_data,1,15) WHEN substr(_data,17,1) = '_' THEN substr(_data,1,16) WHEN substr(_data,18,1) = '_' THEN substr(_data,1,17) WHEN substr(_data,19,1) = '_' THEN substr(_data,1,18) WHEN substr(_data,20,1) = '_' THEN substr(_data,1,19) WHEN substr(_data,21,1) = '_' THEN substr(_data,1,20) WHEN substr(_data,22,1) = '_' THEN substr(_data,1,21) WHEN substr(_data,23,1) = '_' THEN substr(_data,1,22) WHEN substr(_data,24,1) = '_' THEN substr(_data,1,23) WHEN substr(_data,25,1) = '_' THEN substr(_data,1,24) WHEN substr(_data,26,1) = '_' THEN substr(_data,1,25) WHEN substr(_data,27,1) = '_' THEN substr(_data,1,26) WHEN substr(_data,28,1) = '_' THEN substr(_data,1,27) WHEN substr(_data,29,1) = '_' THEN substr(_data,1,28) WHEN substr(_data,30,1) = '_' THEN substr(_data,1,29) WHEN substr(_data,31,1) = '_' THEN substr(_data,1,30) WHEN substr(_data,32,1) = '_' THEN substr(_data,1,31) WHEN substr(_data,33,1) = '_' THEN substr(_data,1,32) WHEN substr(_data,34,1) = '_' THEN substr(_data,1,33) WHEN substr(_data,35,1) = '_' THEN substr(_data,1,34) WHEN substr(_data,36,1) = '_' THEN substr(_data,1,35) WHEN substr(_data,37,1) = '_' THEN substr(_data,1,36) WHEN substr(_data,38,1) = '_' THEN substr(_data,1,37) WHEN substr(_data,39,1) = '_' THEN substr(_data,1,38) WHEN substr(_data,40,1) = '_' THEN substr(_data,1,39) WHEN substr(_data,41,1) = '_' THEN substr(_data,1,40) WHEN substr(_data,42,1) = '_' THEN substr(_data,1,41) ELSE '' END AS result
02-21 20:14:01.627 1690-1690/? D/DBINFO: Extracted First Datapart is short
02-21 20:14:01.627 1690-1690/? D/DBINFO: Extracted First Datapart is thisislonger
02-21 20:14:01.627 1690-1690/? D/DBINFO: Extracted First Datapart is thisisanevenlongerfirstdatapart

This "search and test" approach seem a little bit convoluted but it works. But what if string is too much long? You have to hard code the test for all possible character position — Danilo.NET, Feb 20 '18 at 18:19
@Danilo.NET the real convolution is the design, if well designed distinct columns would be utilised. — MikeT, Feb 21 '18 at 00:51

How to replace instr() function in SQLite?

1 Answers1

EDIT re comment :-