Page 1 of 1

Advanced SQL with parsing example

Posted: Fri Sep 14, 2007 8:16 am
by G0CUZ
Anyone into advanced SQL & Search queries, may be interested in this one which I must admit my Son has helped me with, this is to return partial text strings from the Remarks field in the log, in this example, the returned records are the IOTA references parsed from Remarks in the log and added to a seperate column together with all other log information, this query is added to the Search Window, it could be adapted for other similar purposes - it turned out to be quite complex but works a treat!

SELECT *, Left(Mid(Remarks, InStr(1, Remarks, "IOTA")), Len(Mid(Remarks, InStr(1, Remarks, "IOTA"))) - (Len(Mid(Remarks, InStr(1, Remarks, "IOTA"))) - (IIf(InStr(6, Mid(Remarks, InStr(1, Remarks, "IOTA")), " ") = 0, Len(Mid(Remarks, InStr(1, Remarks, "IOTA"))), InStr(6, Mid(Remarks, InStr(1, Remarks, "IOTA")), " ")-1)))) AS IOTA FROM Log WHERE Remarks like '*IOTA*'

This query can be pasted directly into the Search Window if you want to see the results.



73 Colin G0CUZ
Winlog32 Author