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.

B4X:
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
 

Mahares

Expert
Licensed User
Longtime User
As @Erel suggested, you can alternatively use a parameterized SELECT query to yield the same result set:
B4X:
 txt="SELECT COUNTRY, (length(COUNTRY)  - length(REPLACE(UPPER(COUNTRY), ?, '')))/length(?)  AS MyLen FROM tblCountry" 
    Cursor1=SQL1.ExecQuery2(txt, Array As String(MyString, MyString))
 
Top