Advanced SQL with parsing example

Any useful tips you can offer fellow users can be posted here.
Post Reply
G0CUZ
Site Admin
Posts: 1630
Joined: Wed Jan 02, 2002 7:12 am

Advanced SQL with parsing example

Post by G0CUZ » Fri Sep 14, 2007 8:16 am

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

Post Reply