SQL Cookbook, 2nd Edition

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 cumbersome and frustrating at times. Despite SQL’s limitations, there are some useful built-in functions provided by the different DBMSs, and we’ve tried to use them in creative ways. This chapter in particular is representative of the message we tried to convey in the introduction; SQL is the good, the bad, and the ugly. Hopefully 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 strings can be, while at other times you’ll be aghast by the kind of SQL that is necessary to accomplish a particular task.

Many of the recipes that follow use the TRANSLATE and REPLACE functions that are now available in all the DBMSs covered in this book, with the exception of MySQL, which only has replace. In this last case, it is worth noting early on that you can replicate the effect of TRANSLATE by using nested REPLACE functions.

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 limited loop functionality ...