Search code examples
androidandroid-studioandroid-sqlite

How can I retrieve the longest string from a specific SQLite column in Android Studio using Java?

I am trying to get the longest string from a column in an sqlite db.


        Cursor c = db.getLongest();
        String c1 = c.getString(0);
        String c2 = c.getString(1);
        Log.v("longest name", "longest name: " + c1 + " ~ " + c2 + " ~ " + c.getCount());


    public Cursor getLongest() {
        String sql ="SELECT '_name', LENGTH('_name') count FROM 'tblMain' ORDER BY LENGTH('_name') DESC LIMIT 1;";
        Cursor cur = db.rawQuery(sql, null);
        if (cur != null) {
            cur.moveToFirst();
        }
        return cur;
    }

The above code will print "longest name: _name ~ 5 ~ 1" in the log. It's returning the column name not cell value.

Edit: I have also tried

String sql ="SELECT MAX(LENGTH(_name)) FROM tblMain;";

That will give me the length of the longest but I would really like to get the string or at least the _id of the row.

Solution

  • The above code will print "longest name: _name ~ 5 ~ 1" in the log. It's returning the column

    It's not really returning the column. It's returning the literal value _name and thus 5 as the length as it's the length of the literal. This is because the value is enclosed in single quotes.

    Remove the single quotes around the column name. However, what if two rows have a value that is the same length? (rhetorical - see demo below).

    You might wish to consider the following query (note additional columns have been used so that the results can be demonstrated as below, you would want to omit,re-order or adjust your code to suit your requirements):-

    SELECT *,count() AS _count,length(_name) AS _nameLength, group_concat(_name) AS valuesCSV FROM tblMain WHERE length(_name) =  (SELECT max(length(_name)) FROM tblMain) GROUP BY length(_name);

    This returns a single row (due to the use of GROUP BY and the WHERE clause on the length of the longest value(s)) with all columns from one of the rows that is the longest and additionally:-

    • The number of rows that have the same length as column _count
    • The length of the longest value(s) as column _nameLength
    • all of the values as a CSV as column valuesCSV

    Demo

    The following was used in an SQLite Tool:-

    DROP TABLE IF EXISTS tblMain;
CREATE TABLE IF NOT EXISTS tblMain (id INTEGER PRIMARY KEY, _name TEXT);
INSERT INTO tblMain (_name) VALUES('a1'),('aa2'),('aaa3'),('aaaa4'),('b1'),('bb2'),('bbb3'),('bbbb4'),('a1'),('aa2'),('aaa3'),('aaaa4');
SELECT *,count() AS _count,length(_name) AS _nameLength, group_concat(_name) AS valuesCSV FROM tblMain WHERE length(_name) =  (SELECT max(length(_name)) FROM tblMain) GROUP BY length(_name); 
DROP TABLE IF EXISTS tblMain;

    The result being:-

    enter image description here

    • The ID column is the id of one of the rows as there are 3 rows selected that have the same length in the _name column i.e. (aaaa4, bbbb4 and another aaaa4)
    • Likewise the _name column is one of the 3 names
    • The _count column is 3, as there are 3 rows that are in the group that have the longest value
    • The _nameLength column is 5 as that is the length of the longest value in the _name column
    • The valuesCSV column is a CSV of all of the rows that match the length of the longest value.

    If a row were inserted with a value of zzzzz5 (i.e just 1 row with a length of 6) then the output would be:-

    enter image description here

    P.S

    A Cursor returned from any of the SQLiteDatabase methods that return a Cursor will NEVER be null.