Chapter 9: Text analysis with SQL

There’s a lot of information stored in text form. Even a simple email can reveal us so much: user name (who’s that person), email domain (what company they work for), top-level domain (what country or kind of organization is that). Imagine how much insights we can find in other text columns in our data warehouse.

Text manipulation in SQL

SQL is a very powerful programming language and its power stretches far beyond numeric operations.

We can manipulate text in tons of ways:

• split text into multiple values
• combine multiple text values into one
• convert text to other data types (like dates or numbers)
• filter records by simple inclusion or very complex patterns (regular expressions)
• update existing text values using pattern matching

Pattern matching

A big part of text manipulation are Regular Expressions. It’s a separate language (yep, a programming language inside a programming language) designed to specify complex text patterns.

A Regular Expression is literally a string that is interpreted by SQL engine to find a match in a text value. For example, here’s a regular expressoin that matches a website URL like **sqlhabit.com**: '[\w-]+\.[\w]+'.

Mastering text analysis boils down to learning a handful of SQL operators and text functions. In this chapter you’ll learn all of them, let’s go!