Searching and Filtering

The SQLite provider supports native (SQLite-based) filtering through the use of the -filter parameter in various item cmdlets. For example, this command searches a table for records with a username field value of 'Beefarino':

PS> get-childitem mydb:/Users -filter "username='Beefarino'"

The syntax of the filter parameter is open to the syntax supported by the SQLite WHERE clause. You can create very powerful search using very little PowerShell. This example finds all users with the letter 'f' in their username:

PS> get-childitem mydb:/Users -filter "username like '%f%'"

The -filter parameter is supported on these SQLite provider cmdlets:
  • get-childItem
  • get-item
  • set-item
  • remove-item

This example updates the Status field for all records where the username starts with 'Beef':

PS> set-item mydb:/Users -filter "username like 'beef%'" -value @{ status=0 }


This example removes all database table records with a Status field equal to 0:

PS> remove-item mydb:/Users -filter "status = 0"

Last edited Jan 20, 2012 at 11:57 PM by beefarino, version 1

Comments

beefarino Jan 3, 2013 at 2:05 PM 
You can use the invoke-item cmdlet against the db or table path:

invoke-item db: -sql 'select ... '

ysucaet Jul 29, 2012 at 10:24 PM 
What if you want to run an arbitrary SQL query, like an inner join? It appears that using this code you could only address a single table at the time?

What about the following query:
select b.title, p.url from moz_bookmarks b inner join moz_places p on (b.fk = p.id)