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:

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

Code: 
            '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:

Code: 
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
 
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:

Code: 
            '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:

Code: 
                    '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
 
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:

Code: 
                    '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

GMan
  • Question Question
B4J Question Make native Slider "stepable" ?
Replies
0
Views
46
GMan
GMan
mcqueccu
  • Question Question
Android Question Conflict in merge for keyvaluestore and sqlcipherb4a when R8optimize is true
Replies
0
Views
50
mcqueccu
mcqueccu
J
  • Question Question
Android Question error QR scanner with R8
Replies
1
Views
65
Erel
Erel
Erel
Server software is being upgraded
Replies
3
Views
152
LucaMs
LucaMs
A
eMedNY
Replies
0
Views
108
Alex_197
A
Back
Top