SQL Cookbook

Chapter 6. Working with Strings

This chapter focuses on string manipulation in SQL. Keep in mind that SQL is not designed to perform complex string manipulation and you can (and will) find working with strings in SQL to be very cumbersome and frustrating at times. Despite SQL’s limitations, there are some very useful built-in functions provided by the different DBMSs, and I’ve tried to use them in creative ways. This chapter in particular is very representative of the message I tried to convey in the introduction; SQL is the good, the bad, and the ugly. I hope that you take away from this chapter a better appreciation for what can and can’t be done in SQL when working with strings. In many cases you’ll be surprised by how easy parsing and transforming of strings can be, while at other times you’ll be aghast by the kind of SQL that is necessary to accomplish a particular task.

The first recipe in this chapter is critically important, as it is leveraged by several of the subsequent solutions. In many cases, you’d like to have the ability to traverse a string by moving through it a character at a time. Unfortunately, SQL does not make this easy. Because there is no loop functionality in SQL (Oracle’s MODEL clause excluded), you need to mimic a loop to traverse a string. I call this operation “walking a string” or “walking through a string” and the very first recipe explains the technique. This is a fundamental operation in string parsing when using SQL, and is referenced and used by ...