The SQL Request control is a wonderful addition but it is very limiting in that it only returns two fields, one for display and one for value data. Since I need more data from ther query I've tried concatenating columns in the SQL query. For example:
select (lastname + ', ' + firstname) as displaydata,(phone + ', ' + email) as valuedata from staff where status=1
which gives me the user's full name and a variable with the phone number and email separated by a comma, which I can retrieve with the parseLookup runtime function. What I'd like to do is separate the phone and email values in two calculated fields. But how to do this? The only useful function for string extraction is substring(string, start, length) which works fine when the fixed-length phone number is present:
phone number: subString(parseLookup(DBConnection, false),0,10)
but fails miserably when there is no phone number in the data. What I need is a function, like the java indexOf() method, to tell me the position of the comma so I can use that in combination with the length() function to get the email even when there is no phone number. Is there such a function? If so, why isn't it listed among other runtime functions?
Thanks, in advance.