\n| \n s*(=|<|>|like)<\/b>s*
([^’d][^s’]*)$<\/p>\n<\/td>\n | \n = or < or > or “Like”<\/p>\n<\/td>\n | \n CameraManufacturer=<\/span><\/b>Can%<\/p>\n<\/td>\n<\/tr>\n\n| \n s*(=|<|>|like)s*
([^’d]<\/span><\/b>[^s’]*)$<\/p>\n<\/td>\n | \n Anything that is NOT a ‘ character or a digit<\/p>\n<\/td>\n | \n CameraManufacturer=C<\/span><\/b>an%<\/p>\n<\/td>\n<\/tr>\n\n| \n s*(=|<|>|like)s*
([^’d][^s’]*<\/span><\/b>)$<\/p>\n<\/td>\n | \n Any number of non-quotation mark, non-space characters (or none)<\/p>\n<\/td>\n | \n CameraManufacturer=Can%<\/span><\/b><\/p>\n<\/td>\n<\/tr>\n\n| \n s*(=|<|>|like)s*
([^’d][^s’]*)$<\/span><\/b><\/p>\n<\/td>\n | \n End of line<\/p>\n<\/td>\n | \n <\/p>\n<\/td>\n<\/tr>\n | \n| \n s*(<\/span><\/b>=|<|>|like)<\/span><\/b>s*
(<\/span><\/b>[^’d][^s’]*)<\/span><\/b>$<\/p>\n<\/td>\n | \n Capture the enclosed sections as matches<\/p>\n<\/td>\n | \n $Matches[0]= “=Can%”
$Matches[1]= “=”
$Matches[2]= “Can%”<\/p>\n<\/td>\n<\/tr>\n | \n| \n ‘ $1 ”$2” ‘0<\/p>\n<\/td>\n | \n Replace Matches[0] (“=Can%”) with an expression that uses the two submatches “=” and “can%”.<\/p>\n<\/td>\n | \n = ‘Can%’<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Note<\/b> The expression that is being inserted uses $1 and $2 to mean matches [1] and [2]. If this is wrapped in double quotation marks, Windows PowerShell will try to evaluate these terms before they get to the regex handler, so the replacement string must be wrapped in single quotation marks. But the desired replacement text contains single quotation marks, so they need to be doubled up.<\/p>\nReplace ‘=’ with ‘like’ for wildcards<\/h3>\n So far, =Can* has become =’Can%’, which is good, but SQL needs “LIKE” instead of “=” to evaluate a wildcard. So the next operation converts “CameraManufacturer = ‘Can%’ ” into “CameraManufacturer LIKE ‘Can%’ “.<\/p>\n $Filter = $Filter -replace “s*=s*(?=’.+%’s*$)” ,” LIKE “<\/p>\n \n\n\n| Regular expression<\/b><\/p>\n<\/td>\n | \n Meaning<\/b><\/p>\n<\/td>\n | \n Application<\/b><\/p>\n<\/td>\n<\/tr>\n\n| \n s*=s*<\/span><\/b>(?=’.+%’s*$)<\/p>\n<\/td>\n | \n = sign surrounded by any spaces<\/p>\n<\/td>\n | \n CameraManufacturer = <\/span><\/b>‘Can%’<\/p>\n<\/td>\n<\/tr>\n\n| \n s*=s*(?=‘<\/span><\/b>.+%’s*$)<\/p>\n<\/td>\n | \n A quotation mark character<\/p>\n<\/td>\n | \n CameraManufacturer = ‘<\/span><\/b>Can%’<\/p>\n<\/td>\n<\/tr>\n\n| \n s*=s*(?=’.+<\/span><\/b>%’s*$)<\/p>\n<\/td>\n | \n Any characters (at least one)<\/p>\n<\/td>\n | \n CameraManufacturer = ‘Can<\/span><\/b>%’<\/p>\n<\/td>\n<\/tr>\n\n| \n s*=s*(?=’.+%’<\/span><\/b>s*$)<\/p>\n<\/td>\n | \n % character followed by ‘<\/p>\n<\/td>\n | \n CameraManufacturer = ‘Can%’<\/span><\/b><\/p>\n<\/td>\n<\/tr>\n\n| \n s*=s*(?=’.+%’s*$<\/span><\/b>)<\/p>\n<\/td>\n | \n Any spaces (or none) followed by end of line<\/p>\n<\/td>\n | \n <\/p>\n<\/td>\n<\/tr>\n | \n| \n s*=s*(?=<\/span><\/b>‘.+%’s*$)<\/span><\/b><\/p>\n<\/td>\n | \n Look ahead for the enclosed expression, but don’t include it in the match<\/p>\n<\/td>\n | \n $Matches[0] = “=”
(but only if ‘Can%’ is present)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Provide aliases<\/h3>\nThe previous steps reconstruct “WHERE” terms to build syntactically correct SQL, but what if I get confused and enter CameraMaker instead of CameraManufacturer or Keyword instead of Keywords<\/span>? I need Aliases, and they should work anywhere in the SQL statement—not just in the “WHERE” clause, but also in “ORDER BY”.\nI defined a hash table (aka a “dictionary” or an “associative array”) near the top of the script to act as a single place to store the aliases with their associated full canonical names, like this:<\/p>\n$PropertyAliases = @{Width=”System.Image.HorizontalSize”;<\/p>\n Height=”System.Image.VerticalSize”;<\/p>\n Name=”System.FileName”;<\/p>\n Extension=”System.FileExtension”;<\/p>\n Keyword=”System.Keywords”;<\/p>\n CameraMaker=”System.Photo.CameraManufacturer }\nLater in the script, after the SQL statement is built, a loop runs through the aliases replacing each with its canonical name:<\/p>\n $PropertyAliases.Keys | ForEach-Object {
$SQL= $SQL -replace “(?<=s)$($_)(?=s*(=|>|<|,|Like))”,$PropertyAliases[$_]<\/p>\n }\nA hash table has .Keys<\/b> and .Values<\/b> properties, which return what is on the left and right of the equals sign respectively. $hashTable.keyName or $hashtable[keyName] will return the value, so $_<\/b> will start by taking the value “width”, and its replacement will be $PropertyAliases[“width”], which is “System.Image.HorizontalSize”. On the next pass through the loop, “height” is replaced, and so on. To ensure that it matches on a field name and not text being searched for, the regular expression stipulates that the name must be preceded by a space and followed by “=”or “like”, and so on.<\/p>\n\n\n\n| Regular expression<\/b><\/p>\n<\/td>\n | \n Meaning<\/b><\/p>\n<\/td>\n | \n Application <\/b><\/p>\n<\/td>\n<\/tr>\n\n| \n (?<=s)Width<\/span><\/b>(?=s*(=|>|<|,|Like))<\/p>\n<\/td>\n | \n The literal text “Width”<\/p>\n<\/td>\n | \n Width<\/span><\/b> > 1024<\/p>\n<\/td>\n<\/tr>\n\n| \n (?<=s<\/span><\/b>)Width(?=s*(=|>|<|,|Like))<\/p>\n<\/td>\n | \n A space<\/p>\n<\/td>\n | \n <\/p>\n<\/td>\n<\/tr>\n | \n| \n (?<=<\/span><\/b>s)<\/b>Width(?=s*(=|>|<|,|Like))<\/p>\n<\/td>\n | \n Look behind for the enclosed expression, but don’t include it in the match.<\/p>\n<\/td>\n | \n $Matches[0] = “Width”
(but only if a leading space is present)<\/p>\n<\/td>\n<\/tr>\n | \n| \n (?<=s)Width(?=s*<\/span><\/b>(=|>|<|,|Like))<\/p>\n<\/td>\n | \n Any spaces (or none)<\/p>\n<\/td>\n | \n <\/p>\n<\/td>\n<\/tr>\n | \n| \n (?<=s)Width(?=s*(=|>|<|,|Like)<\/span><\/b>)<\/p>\n<\/td>\n | \n The literal text “Like”, or any of the following characters: comma, equals, greater than, or less than<\/p>\n<\/td>\n | \n Width ><\/span><\/b> 1024<\/p>\n<\/td>\n<\/tr>\n\n| \n (?<=s)Width(?=<\/span><\/b>s*(=|>|<|,|Like))<\/span><\/b><\/p>\n<\/td>\n | \n Look ahead for the enclosed expression, but don’t include it in the match.<\/p>\n<\/td>\n | \n $Matches[0] = “Width”
(but only if ” >” is present)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Add the correct prefix if it is omitted<\/h3>\nThis builds on the ideas we’ve seen already. I want the list of fields and prefixes to be easy to maintain, so just after I define my aliases, I define a list of field types:<\/p>\n $FieldTypes = “System”,”Photo”,”Image”,”Music”,”Media”,”RecordedTv”,”Search”\nFor each type, I define two variables, a prefix<\/b> and a fieldslist<\/b>. The names must be Fieldtype<\/i>PREFIX and FieldType<\/i>FIELDS. The reason for this will become clear shortly, but here is what they look like:<\/p>\n $SystemPrefix = “System.”<\/p>\n $SystemFields = “ItemName|ItemUrl”<\/p>\n $PhotoPrefix = “System.Photo.”
$PhotoFields = “cameramodel|cameramanufacturer|orientation”\nIn practice, the field lists are much longer. System contains 25 field names, not just the two shown here. The lists are written with “|” between the names so they become a regular expression meaning <\/i>“ItemName or ItemUrl Or …”. The following code runs after aliases have been processed:<\/p>\n foreach ($type in $FieldTypes) {<\/p>\n $fields = (get-variable “$($type)Fields”).value<\/p>\n $prefix = (get-variable “$($type)Prefix”).value<\/p>\n $sql = $sql -replace “(?<=s)(?=($Fields)s*(=|>|<|,|Like))” , $Prefix<\/p>\n }\nI can save repeating code by using Get-Variable<\/b> in a loop to get $system<\/span>Fields<\/b>, $photo<\/span>Fields<\/b>, and so on. If I want to add one more field or a whole type, I only need to change the variable declarations at the start of the script. The regular expression in the -Replace<\/b> works like this:<\/p>\n\n\n\n| Regular expression<\/b><\/p>\n<\/td>\n | \n Meaning<\/b><\/p>\n<\/td>\n | \n Application<\/b><\/p>\n<\/td>\n<\/tr>\n\n| \n (?<=s)<\/span><\/b>(?=(cameramanufacturer|
orientation)s*(=|>|<|,|Like))”<\/p>\n<\/td>\n | \n Look behind for a space, but don’t include it in the match.<\/p>\n<\/td>\n | \n <\/p>\n<\/td>\n<\/tr>\n | \n| \n (?<=s)(?=(cameramanufacturer|
orientation<\/span><\/b>)s*(=|>|<|,|Like))”<\/p>\n<\/td>\n | \n The literal text “orientation” or “cameramanufacturer”<\/p>\n<\/td>\n | \n CameraManufacturer LIKE ‘Can%’<\/p>\n<\/td>\n<\/tr>\n | \n| \n (?<=s)(?=(cameramanufacturer|
orientation)s*<\/span><\/b>(=|>|<|,|Like))”<\/p>\n<\/td>\n | \n Any spaces (or none)<\/p>\n<\/td>\n | \n <\/p>\n<\/td>\n<\/tr>\n | \n| \n (?<=s)(?=(cameramanufacturer|
orientation)s*(=|>|<|,|Like)<\/span><\/b>)”<\/p>\n<\/td>\n | \n The literal text “Like”, or any of the following characters: comma, equals, greater than, or less than<\/p>\n<\/td>\n | \n CameraManufacturer LIKE<\/span><\/b> ‘Can%’<\/p>\n<\/td>\n<\/tr>\n\n| \n (?<=s)(?=<\/span><\/b>(cameramanufacturer|
orientation)s*(=|>|<|,|Like))<\/span><\/b>“<\/p>\n<\/td>\n | \n Look ahead for the enclosed expression, but don’t include it in the match.<\/p>\n<\/td>\n | \n $match[0] is the point between the leading space and “CameraManufacturer LIKE”, but it doesn’t include either.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n Use ‑Replace with a regular expression<\/h3>\nWe get the effect of an “insert” operator by using ‑Replace<\/b> with a regular expression that finds a place in the text, but doesn’t select any of it.\nThis part of the function allows “CameraManufacturer LIKE ‘Can%'” to become “System.Photo CameraManufacturer LIKE ‘Can%’ ” in a WHERE clause. I also wanted “CameraManufacturer” in an ORDER BY clause to become “System.Photo CameraManufacturer”.\nVery sharp-eyed readers may have noticed that I look for a comma after the fieldname in addition to <, >, =, and LIKE. I modified the code that appeared in Part One so that when an ORDER BY clause is inserted, it is followed by a trailing comma like this:<\/p>\n if ($orderby) { $sql += ” ORDER BY ” + ($OrderBy -join ” , ” ) + “,”}\nThe new version will work with this regular expression, but the extra comma will cause a SQL error, so it must be removed later. When I introduced the SQL, I said the SELECT statement looks like this:<\/p>\n SELECT System.ItemName, System.ItemUrl, System.FileExtension, System.FileName, System.FileAttributes, System.FileOwner, System.ItemType, System.ItemTypeText , System.KindText, System.Kind, System.MIMEType, System.Size\nBuilding this clause from the field lists simplifies code maintenance, and as a bonus, anything declared in the field lists will be retrieved by the query and accepted as input by its short name. The SELECT clause is prepared like this:<\/p>\n if ($First) {$SQL = “SELECT TOP $First “}<\/p>\n else {$SQL = “SELECT “}<\/p>\n foreach ($type in $FieldTypes) {<\/p>\n $SQL += ((get-variable “$($type)Fields”).value -replace “|”,”, ” ) + “, “<\/p>\n }\nThis replaces the “|” with a comma and puts a comma after each set of fields. This means that there is a comma between the last field and FROM. This allows the regular expression to recognize field names, but it will break the SQL, so it is removed after the prefixes have been inserted (just like ORDER BY).\nThis might seem inefficient, but when I checked the time it took to run the function and get the results (but not output them), it was typically about 0.05 seconds (50 ms) on my laptop. It takes more time to output the results.\nCombining all the bits in this part with the bits in Part One turns my 36-line function into about a 60-line one as follows:<\/p>\n Function Get-IndexedItem{<\/strong><\/p>\nParam ( [Alias(“Where”,”Include”)][String[]]$Filter ,<\/p>\n [Alias(“Sort”)][String[]]$OrderBy,<\/p>\n [Alias(“Top”)][String[]]$First,<\/p>\n [String]$Path,<\/p>\n [Switch]$Recurse )<\/p>\n <\/p>\n $PropertyAliases = @{Width =”System.Image.HorizontalSize”;<\/p>\n Height = “System.Image.VerticalSize”}<\/p>\n $FieldTypes = “System”,”Photo”<\/p>\n $PhotoPrefix = “System.Photo.”<\/p>\n $PhotoFields = “cameramodel|cameramanufacturer|orientation”<\/p>\n $SystemPrefix = “System.”<\/p>\n $SystemFields = “ItemName|ItemUrl|FileExtension|FileName”<\/p>\n <\/p>\n if ($First) {$SQL = “SELECT TOP $First “}<\/p>\n else {$SQL = “SELECT “}<\/p>\n foreach ($type in $FieldTypes) {<\/p>\n $SQL += ((get-variable “$($type)Fields”).value -replace “|”,”, “)+”, “<\/p>\n }<\/p>\n <\/p>\n if ($Path -match “\\\\([^\\]+)\\.”) {<\/p>\n $SQL += ” FROM $($matches[1]).SYSTEMINDEX WHERE ” <\/p>\n }<\/p>\n else {$SQL += ” FROM SYSTEMINDEX WHERE “}<\/p>\n <\/p>\n if ($Filter) {<\/p>\n $Filter = $Filter -replace “*”,”%”<\/p>\n $Filter = $Filter -replace”s*(=|<|>|like)s*([^’d][^s’]*)$”,<\/p>\n ‘ $1 ”$2” ‘<\/p>\n $Filter = $Filter -replace “s*=s*(?=’.+%’s*$)” ,” LIKE “<\/p>\n $Filter = ($Filter | ForEach-Object {<\/p>\n if ($_ -match “‘|=|<|>|like|contains|freetext”) {$_}<\/p>\n else {“Contains(*,’$_’)”}<\/p>\n })<\/p>\n $SQL += $Filter -join ” AND “<\/p>\n }<\/p>\n <\/p>\n if ($Path) {<\/p>\n if ($Path -notmatch “w{4}:”) {$Path = “file:” + $Path}<\/p>\n $Path = $Path -replace “\\”,”\/”<\/p>\n if ($SQL -notmatch “WHEREs$”) {$SQL += ” AND ” }<\/p>\n if ($Recurse) {$SQL += ” SCOPE = ‘$Path’ “}<\/p>\n else {$SQL += ” DIRECTORY = ‘$Path’ “}<\/p>\n }<\/p>\n if ($SQL -match “WHEREs*$”) {<\/p>\n Write-warning “You need to specify either a path , or a filter.” ; return<\/p>\n }<\/p>\n <\/p>\n if ($OrderBy) { $SQL += ” ORDER BY ” + ($OrderBy -join ” , ” ) + “,”}<\/p>\n <\/p>\n $PropertyAliases.Keys | ForEach-Object {<\/p>\n $SQL= $SQL -replace “(?<=s)$($_)(?=s*(=|>|<|,|Like))”,
$PropertyAliases.$_<\/p>\n }<\/p>\n foreach ($type in $FieldTypes) {<\/p>\n $fields = (get-variable “$($type)Fields”).value<\/p>\n $prefix = (get-variable “$($type)Prefix”).value<\/p>\n $SQL = $SQL -replace “(?<=s)(?=($Fields)s*(=|>|<|,|Like))” , $Prefix<\/p>\n }<\/p>\n <\/p>\n $SQL = $SQL -replace “s*,s*FROMs+” , ” FROM “<\/p>\n $SQL = $SQL -replace “s*,s*$” , “”<\/p>\n <\/p>\n $Provider=”Provider=Search.CollatorDSO;”+
“Extended Properties=’Application=Windows’;”<\/p>\n $Adapter = new-object system.data.oledb.oleDBDataadapter -argument $SQL,
$Provider<\/p>\n $DS = new-object system.data.dataset<\/p>\n if ($Adapter.Fill($DS)) { $DS.Tables[0] }<\/p>\n }\n~James\nAwesome job, James! I want to thank you for taking the time to share with us today. Guest Blogger Week will continue tomorrow when James returns with Part Three.\nI invite you to follow me on Twitter<\/a> and Facebook<\/a>. If you have any questions, send email to me at scripter@microsoft.com<\/a>, or post your questions on the Official Scripting Guys Forum<\/a>. See you tomorrow. Until then, peace.\nEd Wilson, Microsoft Scripting Guy<\/b> <\/p>\n","protected":false},"excerpt":{"rendered":"Summary: Guest blogger, James O’Neill, uses Windows PowerShell to help users with input for searching the Windows Index. Microsoft Scripting Guy, Ed Wilson, is here. Today James O’Neill is back with Part Two. Note: This is Part Two of a three part series about using Windows PowerShell to search the Windows Index. Yesterday, James talked […]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[56,353,31,3,4,147,45],"class_list":["post-8931","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-guest-blogger","tag-james-oneill","tag-operating-system","tag-scripting-guy","tag-scripting-techniques","tag-search","tag-windows-powershell"],"acf":[],"blog_post_summary":" Summary: Guest blogger, James O’Neill, uses Windows PowerShell to help users with input for searching the Windows Index. Microsoft Scripting Guy, Ed Wilson, is here. Today James O’Neill is back with Part Two. Note: This is Part Two of a three part series about using Windows PowerShell to search the Windows Index. Yesterday, James talked […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/8931","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=8931"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/8931\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=8931"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=8931"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=8931"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} | | | | | | | | | | | | | | | | |