1. *** New version of B4J is available ***
    B4J v7.8
    Dismiss Notice

Android Code Snippet Exploring SQLite Core Functions, Part Two

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

  1. Mahares

    Mahares Well Known Member Licensed User

    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:

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

    Devan Member Licensed User

    Very nice thank you.
     
  3. ilan

    ilan Expert Licensed User

    Cool, thank you. This is very useful.:)
     
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