Android Code Snippet Exploring SQLite Core Functions, Part Two

Description:

This is the second part of a three part series regarding exploring SQLite core functions which are explained in the following link, http://www.sqlite.org/lang_corefunc.html but lack examples. In this snippet I show examples for each of the next 13 functions that are available in SQLite by default . The SQL statement syntax and examples are listed below for these 13 functions:

sqlite_version(), sqlite_source_id(), RANDOM(), REPLACE(X,Y, Z), SUBSTR(X,Y,Z), SUBSTR(X,Y), TRIM(X), RTRIM(X), LTRIM(X), RTRIM(X,Y), LTRIM(X,Y), TRIM(X,Y), total_changes()


All examples were tested on a device that have SQLite version of 3.7.11 and all 13 passed the test and confirmed that they can be run on any version equal or higher:

'sqlite_version(): Below SQLite version shows on device: e.g. 3.8.6.1
    Dim sqlite As String =SQL1.ExecQuerySingleResult("SELECT sqlite_version()") 
    Log(sqlite)

    'sqlite_source_id(): below shows a date and time, the date and time that the source code was checked in: e.g.: 2015-05-21 17:24:32 b3bb660af9472e2c511d1fe87b5193256f74c0db
    Dim sqlitesource As String =SQL1.ExecQuerySingleResult("SELECT sqlite_source_id()") 
    Log(sqlitesource)

    'RANDOM(), REPLACE(X,Y, Z), SUBSTR(X,Y,Z): below random, Replace and subtr:
    MyQuery="SELECT random() AS rand, REPLACE(COLOR_NAME, 'flower','rose') AS U, substr(COLOR_NAME, 5,4) AS L FROM " & DBTableName & " WHERE COLOR_NAME='cornflowerblue'" 
    Cursor1=SQL1.ExecQuery(MyQuery) 
    Cursor1.Position=0 
    Log($"${Cursor1.GetLong("rand")} ${Cursor1.GetString("U")}   ${Cursor1.GetString("L")} "$) '3908847478,cornroseblue,flow

    'SUBSTR(X,Y): below subtr(X,Y):Returns a string from content of column COLOR_NAME, where the new string starts at
    'the 5th character of COLOR_NAME and extends until the end of the content. It returns: flowerblue
    MyQuery="SELECT  substr(COLOR_NAME, 5) AS L FROM " & DBTableName & " WHERE COLOR_NAME='cornflowerblue'" 
    Cursor1=SQL1.ExecQuery(MyQuery) 
    Cursor1.Position=0 
    Log($"${Cursor1.GetString("L")} "$) 
   
    'TRIM, RTRIM, LTRIM: below Trim, Ltrim and Rtrim  for trimming all spaces both ends, left side and right side respectively
    MyQuery="SELECT TRIM(COLOR_NAME) AS T, LTRIM(COLOR_NAME) AS L, RTRIM(COLOR_NAME) AS R FROM " & DBTableName & " WHERE COLOR_NAME='cornflowerblue'" 
    Cursor1=SQL1.ExecQuery(MyQuery) 
    Cursor1.Position=0 
    Log($"${Cursor1.GetString("T")} ${Cursor1.GetString("L")}   ${Cursor1.GetString("R")} "$)

    'RTRIM(X,Y): Below The rtrim(X,Y) function returns a string formed by removing any and all characters that appear in Y 
    'from the right side of X The result string is: cornflower:
    MyQuery="SELECT Rtrim(COLOR_NAME,'e') FROM " & DBTableName & " WHERE COLOR_NAME='cornflowerblue'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )
   
    'RTRIM(X,Y): below another example: the result string is TestColor formed by removing COLOR-RGB col content 
    'which is 1000 from right of COLOR_NAME column content which is: TestColor 1000:
    MyQuery="SELECT Rtrim(COLOR_NAME,COLOR_RGB) FROM " & DBTableName & " WHERE COLOR_NAME='TestColor 1000'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

    'LTRIM(X,Y): The ltrim(X,Y) function returns a string formed by removing any and all characters that appear in Y 
    'from the left side of X. below returns: Color 1000
    MyQuery="SELECT Ltrim(COLOR_NAME,'Test') FROM " & DBTableName & " WHERE COLOR_NAME='TestColor 1000'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

    'TRIM(X,Y): The trim(X,Y) function returns a string formed by removing any and all characters that appear in Y from 
    'both ends of X. e.g. returns color 1 after Test and 000 were removed from both ends of TestColor 1000
    MyQuery="SELECT trim(COLOR_NAME,'Test 0') FROM " & DBTableName & " WHERE COLOR_NAME='TestColor 1000'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )
   
'    total_changes(): below returns the total number of database rows that were changed, inserted Or deleted since the last connection
    MyQuery="SELECT total_changes() FROM " & DBTableName '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

Tags:All 13 function names featured in this second part series.

Dependency:SQL Library 1.30

 

[Devan]

Very nice thank you.

 

[ilan]

Cool, thank you. This is very useful.