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

Discussion in 'Code Snippets' started by Mahares, Sep 9, 2015.

  1. Mahares

    Mahares Well Known Member Licensed User

    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.

    Code:
    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
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    You should use ExecQuery2 with a parameterized query instead.
     
    Peter Simpson likes this.
  3. Mahares

    Mahares Well Known Member Licensed User

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

    Erel Administrator Staff Member Licensed User

    Now it will work if someone searches for: King's Cross, London (and it will also be safe from SQL injections).
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice