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.
Tags:All 12 function names featured in this third part series, resultset
Dependency:SQL Library 1.30
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.
B4X:
'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")}"$ )
LoopTags:All 12 function names featured in this third part series, resultset
Dependency:SQL Library 1.30
