Parsing a query string

[markl]

'-------------------------------------------------------
'-Parsing a character string from an SQlite table query-
'-------------------------------------------------------
' The Sample.db3 database is a demo created from the
' install of SharpPlus SqliteDeveloper and copied into
' the rbp folder for access
'-------------------------------------------------------
sqliteconnect #mySQLdb, "Sample.db3"

'select all fields in table
query$ = "select * from EMPLOYEE order by LAST_NAME asc;"

'select only specified fields
'query$ = "select FIRST_NAME, LAST_NAME from EMPLOYEE order by LAST_NAME asc;"

#mySQLdb execute(query$)

while #mySQLdb hasanswer()
result$ = #mySQLdb nextrow$("|") 'I prefer using a {|} as a delimiter
print ""
print "===S==T==A==R==T==="
strlength = LEN(TRIM$(result$))
print "The strings length is ";
print strlength;
print " and contains ";
print TRIM$(result$)

check$=""
q=1 ' set midstring starting point
for n = 1 to strlength
check$ = MID$(result$,n,1)
'----------------------------------------------------
'------ Debugging variables for display -----------
'-------uncomment print statements to use------------
'----------------------------------------------------
' print "======"
' print check$ + " = ";
' print "variable q = ";
' print q
' print "for loop counter n is ";
' print n
'----------------------------------------------------
if check$ = "|" then GOSUB [SPLIT]
x=x+1
next n
print "------>Last parsed field is ";
final$ = mid$(result$,q,n)
print final$
wend

#mySQLdb disconnect()
end

'-------------------------------------------------------
'---- subroutine for parsing string delimiter --------
'-------------------------------------------------------
[SPLIT]
final$ = mid$(result$,q,n-q)
print "parsed field = ";
print final$
q=n+1
return



------------------------------------------
QUERY OUTPUT BELOW

Output would be created as follows:

===S==T==A==R==T===
The strings length is 62 and contains 34|Janet|Baldwin|2|1991/03/21|110|Sales|3|USA|61637.81d|00AABB
parsed field = 34
parsed field = Janet
parsed field = Baldwin
parsed field = 2
parsed field = 1991/03/21
parsed field = 110
parsed field = Sales
parsed field = 3
parsed field = USA
parsed field = 61637.81d
------>Last parsed field is 00AABB

===S==T==A==R==T===
The strings length is 63 and contains 105|Oliver H.|Bender|255|1992/10/08|000|CEO|1|USA|212850|00AABB
parsed field = 105
parsed field = Oliver H.
parsed field = Bender
parsed field = 255
parsed field = 1992/10/08
parsed field = 000
parsed field = CEO
parsed field = 1
parsed field = USA
parsed field = 212850
------>Last parsed field is 00AABB

===S==T==A==R==T===
The strings length is 59 and contains 28|Ann|Bennet|5|1991/02/01|120|Admin|5|England|22935|00AABB
parsed field = 28
parsed field = Ann
parsed field = Bennet
parsed field = 5
parsed field = 1991/02/01
parsed field = 120
parsed field = Admin
parsed field = 5
parsed field = England
parsed field = 22935
------>Last parsed field is 00AABB

...................... continuing to end of query

[Jerry Muelver]

For a string delimited with "|", you could parse it like this:

a$ = "one|two|three|four|five"
ix = 1
while (word$(a$,ix,"|") <> "")
   print word$(a$,ix,"|")
   ix = ix + 1
wend


[markl]

'-------------------------------------------------------
'-Parsing a character string from an SQlite table query-
'-------------------------------------------------------
' The Sample.db3 database is a demo created from the
' install of SharpPlus SqliteDeveloper and copied into
' the rbp folder for access
'-------------------------------------------------------
sqliteconnect #mySQLdb, "Sample.db3"

'select all fields in table
query$ = "select * from EMPLOYEE order by LAST_NAME asc;"

'select only specified fields
'query$ = "select FIRST_NAME, LAST_NAME from EMPLOYEE order by LAST_NAME asc;"

#mySQLdb execute(query$)

while #mySQLdb hasanswer()
result$ = #mySQLdb nextrow$("|") 'I prefer using a {|} as a delimiter
print ""
print "===S==T==A==R==T==="
ix = 1
while (word$(result$,ix,"|") <> "")
print word$(result$,ix,"|")
ix = ix + 1
wend
wend

#mySQLdb disconnect()
end


Updated with the WORD$ function makes it much cleaner.