Advanced SQL with parsing example

Any useful tips you can offer fellow users can be posted here.

Advanced SQL with parsing example

Postby 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
G0CUZ
Site Admin
 
Posts: 1602
Joined: Wed Jan 02, 2002 7:12 am
 
 

Return to Hints and Tips

cron