Android Code Snippet Count The Number of a Given String In a SQLite Column

Description: To look for any given sub string inside a SQLite table column, similar to @stevel05 code snippet, but here I show the SQL statement equivalent. For example: If my search sub string is 'ic', the country of 'Dominican Republic' will show 2.

txt="CREATE TABLE IF NOT EXISTS  tblCountry (COUNTRY TEXT PRIMARY KEY)"
    SQL1.ExecNonQuery(txt)  
    txt="INSERT INTO tblCountry  VALUES(?)"

    SQL1.ExecNonQuery2(txt, Array As String("CHINA"))
    SQL1.ExecNonQuery2(txt, Array As String("INDIA"))
    SQL1.ExecNonQuery2(txt, Array As String("USA"))
    SQL1.ExecNonQuery2(txt, Array As String("INDONESIA"))
    SQL1.ExecNonQuery2(txt, Array As String("BRAZIL"))
    SQL1.ExecNonQuery2(txt, Array As String("PAKISTAN"))
    SQL1.ExecNonQuery2(txt, Array As String("BANGLADESH"))
    SQL1.ExecNonQuery2(txt, Array As String("NIGERIA"))
    SQL1.ExecNonQuery2(txt, Array As String("RUSSIA"))
    SQL1.ExecNonQuery2(txt, Array As String("JAPAN"))
    SQL1.ExecNonQuery2(txt, Array As String("Test to Count the characters in this test string"))
    SQL1.ExecNonQuery2(txt, Array As String("Central African Republic"))
    SQL1.ExecNonQuery2(txt, Array As String("Democratic Republic of Congo"))
    SQL1.ExecNonQuery2(txt, Array As String("Dominican Republic"))

    'Dim MyString As String = "IA"  'works
    'Dim MyString As String = "o".ToUpperCase  'works
    'Dim MyString As String = "republic".ToUpperCase   'works
    'Dim MyString As String = "test".ToUpperCase    'works
    'Dim MyString As String = "ni".ToUpperCase   'works
    Dim MyString As String = "ic".ToUpperCase   'works

    txt="SELECT COUNTRY, (length(COUNTRY)  - length(REPLACE(UPPER(COUNTRY), '" _
    & MyString & "', '')))/length('" & MyString & "')  AS MyLen FROM tblCountry"

    Cursor1=SQL1.ExecQuery(txt)
    For i=0 To Cursor1.RowCount-1
        Cursor1.Position=i
        Log(Cursor1.Getstring("COUNTRY") & "  " &  Cursor1.GetInt("MyLen"))
    Next

Tags: SQLite, ExecQuery, replace, Upper, length
Libray: SQL

 

[Erel]

You should use ExecQuery2 with a parameterized query instead.

 

[Mahares]

As @Erel suggested, you can alternatively use a parameterized SELECT query to yield the same result set:

 txt="SELECT COUNTRY, (length(COUNTRY)  - length(REPLACE(UPPER(COUNTRY), ?, '')))/length(?)  AS MyLen FROM tblCountry" 
    Cursor1=SQL1.ExecQuery2(txt, Array As String(MyString, MyString))

 

[Erel]

Now it will work if someone searches for: King's Cross, London (and it will also be safe from SQL injections).