by Craig S. Mullins
SQL is the lingua franca for modifying and reading database data and any DBA worth his (or her) paycheck should be proficient in writing SQL queries. But SQL is a flexible and feature-
Let’s start out with a simple one. Here is a technique you can deploy when you need to return a count of specific characters in a column using only SQL:
select length('A,B,C,D') -
The length() function returns the length of the character string supplied as the argument. Knowing this, we can use the replace() function to change the character we wish to count (in this case a comma) with an empty string. By subtracting the original length from the length of the modified string we get the number of characters removed. This can be adapted to count any character you wish simply by changing the ‘,’ to whatever character you desire.
Let’s move on to a little more complex one. We all can relate to dealing with systems that have data integrity problems. But some data integrity problems can be cleaned up using a dash of SQL. Consider the common data entry problem of extraneous spaces inserted into a name field. Not only is it annoying, sometimes it can cause the system to ignore relationships between data elements because the names do not match. For example, “Craig Mullins” is not equivalent to “Craig Mullins”; the first one has three spaces between the first and last name whereas the second one only has one.
You can write an update statement to clean up these type problems, if you know how to use the replace() function. As we learned in the first technique, replace()does what it sounds like it would do: it reviews a source string and replaces all occurrences of a one string with another. For example, to replace all occurrences of Z with A in the string BZNZNZ you would code:
replace('BZNZNZ', 'Z', 'A')
And the result would be BANANA. So, let’s code some SQL using the replace() function to get rid of any unwanted spaces in the NAME column of our EMPLOYEE table. Keep in mind that we have no idea how many extra spaces there may be in the NAME columns. One may have two extra spaces, another fifteen extra, and another only one. So the SQL has to be flexible. Consider this:
set NAME = replace(
replace(NAME, space(1), '<>')
Of course, if you don’t like the carats you can use any two characters you like. But the left and right carat characters work well visually. Be sure that you do not choose to use characters that occur naturally in the string that you are acting upon. (I cannot think of anyone with a carat in their name, can you?)
Finally, I used the space() function for clarity. You could have used strings encased in single quotes (as in the first example), but the space() function is easier to read. It simply returns a string of spaces the length of which is specified as the integer argument. For example, space(11) would return a string of eleven spaces.
Keep this technique in your bag of tricks for when you need to clean up dirty data.
As you can see from these two examples, SQL is indeed a very, flexible and powerful language. It is a good idea for DBAs to learn all they can about the features, power, and intricacies of SQL. Frequently, the only thing you’ll need to accomplish many data-
From Database Trends and Applications, November 2010.
© 2012 Craig S. Mullins,