Android Code Snippet Exploring SQLite Core Functions, Part Three

Discussion in 'Code Snippets' started by Mahares, Dec 19, 2016.

  1. Mahares

    Mahares Well Known Member Licensed User


    This is the third part of a three part series of exploring SQLite core functions which are found in the following link, 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)

    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 (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 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}
        LIKE  'medium%'"$

    Dim rs As ResultSet
    Do While rs.NextRow
    Log($"${rs.getstring("color")}"$ )

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

    Dependency:SQL Library 1.30
    Johan Schoeman, fredo and Erel like this.
  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