T-SQL Tuesday #191 - String Parsing

For T-SQL Tuesday #191, Steve Jones (blog) asked about favorite string parsing routines.

When dealing with data inside SQL Server, especially when it’s delimited by a clear separator character, earlier versions (before SQL Server 2016) required us to write custom parsing functions—either as T-SQL user-defined functions or CLR functions. Starting with SQL Server 2016, we can use the built-in STRING_SPLIT() function to handle most of these tasks.

However, more often than not, we need to parse data that resides outside SQL Server—for example, in log files, CSV data, or other data sources. For these cases, I often use Microsoft Log Parser, a free command-line tool available here.

Although the latest version of Log Parser (2.2) was released back in May 2005, it remains surprisingly powerful and versatile. Originally I use the tool to analyze IIS logs, but it can also handle CSV files, Windows Event Logs, Exchange logs, and much more.

Why I Like Log Parser

What I like most about Log Parser is that it allows me to use SQL-like expressions to query data from a variety of sources—such as IIS logs, CSV files, and Windows Event Logs—and output the results in multiple formats, including SQL Server tables, CSV, and more. It even includes several built-in string parsing functions that are quite handy.

Example: Extracting Substrings

Suppose we have a CSV file containing strings like Hello5-E-100, and we want to extract the last number after the dash (-). Just like the question that is on this thread. We can use Log Parser’s EXTRACT_PREFIX function:

logparser "SELECT EXTRACT_PREFIX(data, 0, '-') AS LastNumber FROM data.csv" -i:CSV

Example: Extracting File Names and Extensions

Log Parser also provides functions for working with file paths. If you have a CSV file like this:

Path
C:\tmp\test.dat
D:\Program Files(x86)\Microsoft\Test.xlsx

You can extract just the file names using:

logparser "SELECT EXTRACT_FILENAME(Path) AS FileName FROM data.csv" -i:CSV

Result:

FileName
---------
test.dat
Test.xlsx

Or extract just the file extensions using:

logparser "SELECT EXTRACT_EXTENSION(Path) AS FileExtension FROM data.csv" -i:CSV

Result:

FileExtension
-------------
dat
xlsx

Example: Reverse DNS Lookup

This one isn’t exactly string parsing, but it’s still a neat feature. If you have a CSV file full of IP addresses, Log Parser can perform a reverse DNS lookup to resolve them into host names:

logparser "SELECT REVERSEDNS(data) AS HostName FROM data.csv" -i:CSV

How cool is that?

Final Thoughts

The main downside of Log Parser is that it hasn't been actively developed since 2005. That said, the tool is stable, reliable, and still quite useful for many data parsing tasks. It's definitely worth keeping in your toolbox.