Android Code Snippet Exploring SQLite Core Functions, Part Three

Description:

This is the third part of a three part series of exploring SQLite core functions which are found in the following link, http://www.sqlite.org/lang_corefunc.html All 12 functions described here are available in SQLite by default . The SQL statement syntax and examples are listed below for these 12 functions:

Min(X,Y,..), MAX(X,Y,..), typeof(X), nullif(X,Y), Round(X), Round(X,Y), UNICODE(X)
LIKE(X,Y), LIKELY(X), LIKELIHOOD(X,Y), UNLIKELY(X), QUOTE(X)

All examples were tested on a device that has SQLite version of 3.7.11 (released: 2014-3-20). The four newer ones: UNICODE(X), LIKELY(X), LIKELIHOOD(X,Y), UNLIKELY(X) will not run with SQLite 3.7.11, but all 12 functions ran flawlessly on a device with SQLite version: 3.8.6 (released: 2014-8-15) and 3.8.6.1 (released: 2014-10-22).. As an added small bonus, Smart String Literal and ResultSet (cursor extension) were applied to one of the examples.

'MIN: Below displays the smaller of the value of COLOR_B4A and an arbitrary number -10185250
    MyQuery="SELECT min(COLOR_B4A,-10185250) FROM " & DBTableName & " WHERE COLOR_NAME='cornflowerblue'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

    'MAX:Below displays the bigger of the value of COLOR_B4A (-10185235) and an arbitrary number -10185250
    MyQuery="SELECT max(COLOR_B4A,-10185250) FROM " & DBTableName & " WHERE COLOR_NAME='cornflowerblue'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

    'typeof(X): below returns the data type of the column querried.one of these: "null", "integer", "real", "text", or "blob"
    MyQuery="SELECT typeof(COLOR_B4A) FROM " & DBTableName & " WHERE COLOR_NAME='cornflowerblue'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

    'nullif(X,Y): below returns the number of records where COLOR_B4A column is not -65281
    MyQuery="SELECT count(nullif(COLOR_B4A,-65281)) FROM " & DBTableName
    Log(SQL1.ExecQuerySingleResult(MyQuery) )
   
    'round(X): below rounds 155.676 to 156 without decimals:
    MyQuery="SELECT round(COLOR_B4A) FROM " & DBTableName & " WHERE COLOR_NAME='TestColor4'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )
   
    'round(X,Y): below rounds 155.676 to 155.68 with 2 decimal places:
    MyQuery="SELECT round(COLOR_B4A, 2) FROM " & DBTableName & " WHERE COLOR_NAME='TestColor4'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

    'UNICODE(X): Below: The unicode(X) function returns the numeric unicode code point corresponding to the first
    ' character of the string X: This returned: 98 for blue, 111 for orange, 111 for orchid:
    MyQuery="SELECT unicode(COLOR_NAME) FROM " & DBTableName & " WHERE COLOR_NAME='orchid'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

    'LIKE(X,Y): below returns 1.
    MyQuery="SELECT like(trim(COLOR_NAME),'cornflowerblue') FROM " & DBTableName & " WHERE COLOR_NAME='cornflowerblue'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )
   
    'LIKE(X,Y): below returns 0.
    MyQuery="SELECT like(trim(COLOR_NAME),'corn') FROM " & DBTableName & " WHERE COLOR_NAME='cornflowerblue'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

    'LIKELY(X). Below The likely(X) function returns the argument X unchanged. Returns: cornflowerblue
    MyQuery="SELECT likely(COLOR_NAME) FROM " & DBTableName & " WHERE COLOR_NAME='cornflowerblue'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

    'LIKELIHOOD(X,Y). Below The likelihood(X,Y) function returns argument X unchanged. Y is float number between 0 and 1
    '    It returns: cornflowerblue whether the Y is .34567, .009 or any other beween 0 and 1:
    MyQuery="SELECT likelihood(COLOR_NAME, .129) FROM " & DBTableName & " WHERE COLOR_NAME='cornflowerblue'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

    'UNLIKELY(X): Below the unlikely(X) function returns the argument X unchanged. Similar to LIKELIHOOD(X,Y)
    'It returns: cornflowerblue
    MyQuery="SELECT unlikely(COLOR_NAME) FROM " & DBTableName & " WHERE COLOR_NAME='cornflowerblue'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

    'QUOTE(X): below puts the result of the query between single quotes. All COLOR_NAME contents that start with medium
    'are displayed surrounded by single quotes:'mediumblue', 'mediumorchid', etc.
'    MyQuery="SELECT quote(COLOR_NAME) AS color FROM " & DBTableName & " WHERE COLOR_NAME LIKE  'medium%'"
    MyQuery=$"SELECT quote(COLOR_NAME) AS color FROM  ${DBTableName}
    WHERE COLOR_NAME 
    LIKE  'medium%'"$
    Dim rs As ResultSet
    rs=SQL1.ExecQuery(MyQuery)
    Do While rs.NextRow
        Log($"${rs.getstring("color")}"$ )
    Loop

Tags:All 12 function names featured in this third part series, resultset

Dependency:SQL Library 1.30