What is the azure table storage query equivalent of T-sql's LIKE command?

Question

I'm querying Azure table storage using the Azure Storage Explorer. I want to find all messages that contain the given text, like this in T-SQL:

message like '%SysFn%'

Executing the T-SQL gives "An error occurred while processing this request"

What is the equivalent of this query in Azure?

Answer 1

There's no direct equivalent, as there is no wildcard searching. All supported operations are listed here. You'll see eq, gt, ge, lt, le, etc. You could make use of these, perhaps, to look for specific ranges.

Depending on your partitioning scheme, you may be able to select a subset of entities based on specific partition key, and then scan through each entity, examining message to find the specific ones you need (basically a partial partition scan).

Answer 2

Another option would be export the logs from Azure Table storage to csv. Once you have the csv you can open this in excel or any other app and search for the text.

You can export table storage data using TableXplorer (http://clumsyleaf.com/products/tablexplorer). In this there is an option to export the filtered data to csv.

Answer 3

While an advanced wildcard search isn't strictly possible in Azure Table Storage, you can use a combination of the "ge" and "lt" operators to achieve a "prefix" search. This process is explained in a blog post by Scott Helme here.

Essentially this method uses ASCII incrementing to query Azure Table Storage for any rows whose property begins with a certain string of text. I've written a small Powershell function that generates the custom filter needed to do a prefix search.

Function Get-AzTableWildcardFilter {
    param (
        [Parameter(Mandatory=$true)]
        [string]$FilterProperty,

        [Parameter(Mandatory=$true)]
        [string]$FilterText
    )

    Begin {}

    Process {
        $SearchArray = ([char[]]$FilterText)
        $SearchArray[-1] = [char](([int]$SearchArray[-1]) + 1)
        $SearchString = ($SearchArray -join '')
    }

    End {
        Write-Output "($($FilterProperty) ge '$($FilterText)') and ($($FilterProperty) lt '$($SearchString)')"
    }
}

You could then use this function with Get-AzTableRow like this (where $CloudTable is your Microsoft.Azure.Cosmos.Table.CloudTable object):

Get-AzTableRow -Table $CloudTable -CustomFilter (Get-AzTableWildcardFilter -FilterProperty 'RowKey' -FilterText 'foo')