Android Tutorial SQL (SQLite) InstrRev

SQLite has the string function Instr, which finds the first occurrence of a specified string in another string.
There is no InstrRev function though to find the last occurrence of a string within another string.
As I needed this I made another custom SQL function (I have several of these) to do this.

This is the code part that goes through the SQL and find these custom SQL functions and replaces them with different SQL.
All the variables are local variables and I think the code explains itself.
I added a slight variation to find the last occurrence of a number.
Maybe somebody may find this useful:

B4X: 
            'select xinstrrev(street, ' ')
            '-----------------------------
            strError = "Error in function xinstrrev"
            iFunctionConvertPos = strSQL.ToLowerCase.IndexOf("xinstrrev(")
            Do While iFunctionConvertPos > -1 And strSQL.CharAt(iFunctionConvertPos - 1) <> Chr(39)
                iFunctionConvertEndPos = cMP.GetPosOfClosingBracket(strSQL, iFunctionConvertPos)
                strStringToReplace = strSQL.SubString2(iFunctionConvertPos, iFunctionConvertEndPos + 1)
                iCommaPos = strStringToReplace.IndexOf(",")
                strField = strStringToReplace.SubString2(10, iCommaPos)
                strFindString = strStringToReplace.SubString2(iCommaPos + 2, strStringToReplace.Length - 1)
                strReplace = $"length(rtrim(${strField}, replace(${strField}, ${strFindString}, '')))"$
                strSQL = strSQL.Replace(strStringToReplace, strReplace)
                iFunctionConvertPos = strSQL.ToLowerCase.IndexOf("xinstrrev(")
            Loop
            
            'select xlastnumberpos(street)
            '-----------------------------
            iFunctionConvertPos = strSQL.ToLowerCase.IndexOf("xlastnumberpos(")
            strError = "Error in function xlastnumberpos"
            Do While iFunctionConvertPos > -1 And strSQL.CharAt(iFunctionConvertPos - 1) <> Chr(39)
                iFunctionConvertEndPos = cMP.GetPosOfClosingBracket(strSQL, iFunctionConvertPos) + 1
                strField = strSQL.SubString2(iFunctionConvertPos + 15, iFunctionConvertEndPos - 1)
                strStringToReplace = strSQL.SubString2(iFunctionConvertPos, iFunctionConvertEndPos)
                      
                strSQL = strSQL.Replace(strStringToReplace, _
                $"max(length(rtrim(${strField}, replace(${strField}, '0', ''))), 
                      length(rtrim(${strField}, replace(${strField}, '1', ''))),  
                      length(rtrim(${strField}, replace(${strField}, '2', ''))),  
                      length(rtrim(${strField}, replace(${strField}, '3', ''))),  
                      length(rtrim(${strField}, replace(${strField}, '4', ''))), 
                      length(rtrim(${strField}, replace(${strField}, '5', ''))), 
                      length(rtrim(${strField}, replace(${strField}, '6', ''))),
                      length(rtrim(${strField}, replace(${strField}, '7', ''))), 
                      length(rtrim(${strField}, replace(${strField}, '8', ''))), 
                      length(rtrim(${strField}, replace(${strField}, '9', ''))))"$)
                'Log(strSQL)
                iFunctionConvertPos = strSQL.ToLowerCase.IndexOf("xlastnumberpos(")
            Loop

RBS
 
Click to expand...
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
RB Smissaert said:
SQLite has the string function Instr, which finds the first occurrence of a specified string in another string.
There is no InstrRev function though to find the last occurrence of a string within another string.
As I needed this I made another custom SQL function (I have several of these) to do this.

This is the code part that goes through the SQL and find these custom SQL functions and replaces them with different SQL.
All the variables are local variables and I think the code explains itself.
I added a slight variation to find the last occurrence of a number.
Maybe somebody may find this useful:

B4X: 
            'select xinstrrev(street, ' ')
            '-----------------------------
            strError = "Error in function xinstrrev"
            iFunctionConvertPos = strSQL.ToLowerCase.IndexOf("xinstrrev(")
            Do While iFunctionConvertPos > -1 And strSQL.CharAt(iFunctionConvertPos - 1) <> Chr(39)
                iFunctionConvertEndPos = cMP.GetPosOfClosingBracket(strSQL, iFunctionConvertPos)
                strStringToReplace = strSQL.SubString2(iFunctionConvertPos, iFunctionConvertEndPos + 1)
                iCommaPos = strStringToReplace.IndexOf(",")
                strField = strStringToReplace.SubString2(10, iCommaPos)
                strFindString = strStringToReplace.SubString2(iCommaPos + 2, strStringToReplace.Length - 1)
                strReplace = $"length(rtrim(${strField}, replace(${strField}, ${strFindString}, '')))"$
                strSQL = strSQL.Replace(strStringToReplace, strReplace)
                iFunctionConvertPos = strSQL.ToLowerCase.IndexOf("xinstrrev(")
            Loop
           
            'select xlastnumberpos(street)
            '-----------------------------
            iFunctionConvertPos = strSQL.ToLowerCase.IndexOf("xlastnumberpos(")
            strError = "Error in function xlastnumberpos"
            Do While iFunctionConvertPos > -1 And strSQL.CharAt(iFunctionConvertPos - 1) <> Chr(39)
                iFunctionConvertEndPos = cMP.GetPosOfClosingBracket(strSQL, iFunctionConvertPos) + 1
                strField = strSQL.SubString2(iFunctionConvertPos + 15, iFunctionConvertEndPos - 1)
                strStringToReplace = strSQL.SubString2(iFunctionConvertPos, iFunctionConvertEndPos)
                     
                strSQL = strSQL.Replace(strStringToReplace, _
                $"max(length(rtrim(${strField}, replace(${strField}, '0', ''))),
                      length(rtrim(${strField}, replace(${strField}, '1', ''))), 
                      length(rtrim(${strField}, replace(${strField}, '2', ''))), 
                      length(rtrim(${strField}, replace(${strField}, '3', ''))), 
                      length(rtrim(${strField}, replace(${strField}, '4', ''))),
                      length(rtrim(${strField}, replace(${strField}, '5', ''))),
                      length(rtrim(${strField}, replace(${strField}, '6', ''))),
                      length(rtrim(${strField}, replace(${strField}, '7', ''))),
                      length(rtrim(${strField}, replace(${strField}, '8', ''))),
                      length(rtrim(${strField}, replace(${strField}, '9', ''))))"$)
                'Log(strSQL)
                iFunctionConvertPos = strSQL.ToLowerCase.IndexOf("xlastnumberpos(")
            Loop

RBS
Click to expand...
Just forgot to post the Sub to find the closing bracket:

B4X: 
Sub GetPosOfClosingBracket(strString As String, iStartPos As Int) As Int
    
    Dim i As Int
    Dim iOpenCount As Int
    
    For i = iStartPos To strString.Length
        If strString.CharAt(i) = "(" Then
            iOpenCount = iOpenCount + 1
        Else
            If strString.CharAt(i) = ")" Then
                iOpenCount = iOpenCount - 1
                If iOpenCount = 0 Then
                    Return i
                End If
            End If
        End If
    Next
    
    Return -1
    
End Sub

RBS
 
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
RB Smissaert said:
SQLite has the string function Instr, which finds the first occurrence of a specified string in another string.
There is no InstrRev function though to find the last occurrence of a string within another string.
As I needed this I made another custom SQL function (I have several of these) to do this.

This is the code part that goes through the SQL and find these custom SQL functions and replaces them with different SQL.
All the variables are local variables and I think the code explains itself.
I added a slight variation to find the last occurrence of a number.
Maybe somebody may find this useful:

B4X: 
            'select xinstrrev(street, ' ')
            '-----------------------------
            strError = "Error in function xinstrrev"
            iFunctionConvertPos = strSQL.ToLowerCase.IndexOf("xinstrrev(")
            Do While iFunctionConvertPos > -1 And strSQL.CharAt(iFunctionConvertPos - 1) <> Chr(39)
                iFunctionConvertEndPos = cMP.GetPosOfClosingBracket(strSQL, iFunctionConvertPos)
                strStringToReplace = strSQL.SubString2(iFunctionConvertPos, iFunctionConvertEndPos + 1)
                iCommaPos = strStringToReplace.IndexOf(",")
                strField = strStringToReplace.SubString2(10, iCommaPos)
                strFindString = strStringToReplace.SubString2(iCommaPos + 2, strStringToReplace.Length - 1)
                strReplace = $"length(rtrim(${strField}, replace(${strField}, ${strFindString}, '')))"$
                strSQL = strSQL.Replace(strStringToReplace, strReplace)
                iFunctionConvertPos = strSQL.ToLowerCase.IndexOf("xinstrrev(")
            Loop
           
            'select xlastnumberpos(street)
            '-----------------------------
            iFunctionConvertPos = strSQL.ToLowerCase.IndexOf("xlastnumberpos(")
            strError = "Error in function xlastnumberpos"
            Do While iFunctionConvertPos > -1 And strSQL.CharAt(iFunctionConvertPos - 1) <> Chr(39)
                iFunctionConvertEndPos = cMP.GetPosOfClosingBracket(strSQL, iFunctionConvertPos) + 1
                strField = strSQL.SubString2(iFunctionConvertPos + 15, iFunctionConvertEndPos - 1)
                strStringToReplace = strSQL.SubString2(iFunctionConvertPos, iFunctionConvertEndPos)
                     
                strSQL = strSQL.Replace(strStringToReplace, _
                $"max(length(rtrim(${strField}, replace(${strField}, '0', ''))),
                      length(rtrim(${strField}, replace(${strField}, '1', ''))), 
                      length(rtrim(${strField}, replace(${strField}, '2', ''))), 
                      length(rtrim(${strField}, replace(${strField}, '3', ''))), 
                      length(rtrim(${strField}, replace(${strField}, '4', ''))),
                      length(rtrim(${strField}, replace(${strField}, '5', ''))),
                      length(rtrim(${strField}, replace(${strField}, '6', ''))),
                      length(rtrim(${strField}, replace(${strField}, '7', ''))),
                      length(rtrim(${strField}, replace(${strField}, '8', ''))),
                      length(rtrim(${strField}, replace(${strField}, '9', ''))))"$)
                'Log(strSQL)
                iFunctionConvertPos = strSQL.ToLowerCase.IndexOf("xlastnumberpos(")
            Loop

RBS
Click to expand...
After using the custom function xlastnumberpos for a while I realised that the posted code makes this quite slow.
Use instead this, which is nearly 10 times faster:

B4X: 
                    'Can leave the coalesce out if you are not interested to know that the field contained a Null value
                    strSQL = strSQL.Replace(strStringToReplace, _
                    $"coalesce(max(
instr(${strField}, 0),
instr(${strField}, 1),
instr(${strField}, 2),
instr(${strField}, 3),
instr(${strField}, 4),
instr(${strField}, 5),
instr(${strField}, 6),
instr(${strField}, 7),
instr(${strField}, 8),
instr(${strField}, 9)), - 1)"$)

RBS
 
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
RB Smissaert said:
After using the custom function xlastnumberpos for a while I realised that the posted code makes this quite slow.
Use instead this, which is nearly 10 times faster:

B4X: 
                    'Can leave the coalesce out if you are not interested to know that the field contained a Null value
                    strSQL = strSQL.Replace(strStringToReplace, _
                    $"coalesce(max(
instr(${strField}, 0),
instr(${strField}, 1),
instr(${strField}, 2),
instr(${strField}, 3),
instr(${strField}, 4),
instr(${strField}, 5),
instr(${strField}, 6),
instr(${strField}, 7),
instr(${strField}, 8),
instr(${strField}, 9)), - 1)"$)

RBS
Click to expand...
I was using this to get street names, leaving off the leading number and it that situation the above will usually be OK, but it will for example fail if used in something like this:

1 Church Street Flat 1

RBS
 
You must log in or register to reply here.

Similar Threads

netsistemas
  • Question
Android Question Decode SQL to string with params (not is a question)
Replies
8
Views
1K
netsistemas
netsistemas
J
  • Question
Android Question Get Query Result as String MySql jRDC2 Help!
Replies
5
Views
1K
josejad
josejad
M
  • Question
Android Question SQL 2 SQL, Double some rows
Replies
2
Views
1K
mw71
M
Harris
  • Article
Android Code Snippet DBUtils 2 - New Method - HasRecords
2
Replies
23
Views
7K
Harris
Harris
Emme Developer
Bug? ExecQueryAsync with "?"
Replies
7
Views
3K
Emme Developer
Emme Developer
Top