"Windows Search" with SQL

This is a 2nd in a series of "How I use SQL daily on my Windows7". 1st one was about Firefox download history.

Today's SQL does sort of the equivalent of this GUI.



Firstly, I downloaded C# source and compiled WSSQL.exe executable file.

Windows Search Sample Code - Home

Windows Search through Structured Query Language (SQL)


Next, I run this WSSQL.exe with long SQL text given as 1st arg.

Below is an example of SQL and results.
I searched for files with the word "snmpSubscriber" in full path or its contents and
the SQL returned 3 columns where 2nd column shows indexed time and 3rd column
shows how many times the word appears in file content.

I could find 5 pdf files which are Exadata docs.

$ WSSQL.exe \
> "SELECT System.ItemPathDisplay,System.Search.GatherTime,System.Search.HitCount \
>  FROM SystemIndex       \
>  where contains(*,'snmpSubscriber') And System.Kind !='email'   \
>  order by System.Search.HitCount"
Query=SELECT System.ItemPathDisplay,System.Search.GatherTime,System.Search.HitCount  FROM SystemIndex        where contains(*,'snmpSubscriber') And System.Kind !='email'    order by System.Search.HitCount
C:\kinoue\materials\Exadata.Admin.Partners\e13862.pdf;2014/01/04 18:33:49;4;
C:\kinoue\materials\Exadata.Admin.Partners\e23333.pdf;2014/01/04 0:48:43;8;
C:\kinoue\materials\Exadata.Admin.Partners\e27442.pdf;2014/01/04 0:48:37;11;
C:\kinoue\materials\Exadata.Admin.Partners\e13874.pdf;2014/01/04 0:50:17;12;
C:\kinoue\materials\Exadata.Admin.Partners\e13861.pdf;2014/01/04 0:50:32;16;

BTW, Oracle Database has full text search capability for over many releases and CONTAINS() operator works similarly.

Oracle Text SQL Statements and Operators

Use the CONTAINS operator in the WHERE clause of a SELECT statement to specify the query expression for a Text query.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today