More Character Functions - MySQL

Null Value Function (NVL, ISNULL, IFNULL)

Oracle, Microsoft SQL Server, and MySQL all provide a function that replaces null values with a selected value. Unfortunately, they each give the function a different name: NVL in Oracle, ISNULL in SQL Server, and IFNULL in MySQL. Apparently, DB2 has no equivalent function. The following examples select the LATE_OR_LOSS_FEE from the MOVIE_RENTAL table with null values replaced by 0. Transaction 9 was selected because it has two movies on it, one of which has a null value for LATE_OR_LOSS_FEE (a good example that shows that null values are transformed while non-null values are left just the way they are).

   Oracle:

SELECT NVL(LATE_OR_LOSS_FEE, 0) AS LATE_OR_LOSS_FEE
 FROM MOVIE_RENTAL
WHERE TRANSACTION_ID=9;
LATE_OR_LOSS_FEE
----------------
               0
           29.98
2 rows selected.

Microsoft SQL Server:

SELECT ISNULL(LATE_OR_LOSS_FEE, 0) AS LATE_OR_LOSS_FEE
  FROM MOVIE_RENTAL
WHERE TRANSACTION_ID=9;
LATE_OR_LOSS_FEE
----------------
           29.98
             .00
 (2 rows affected)

   MySQL:

SELECT IFNULL(LATE_OR_LOSS_FEE, 0) AS LATE_OR_LOSS_FEE
  FROM MOVIE_RENTAL
WHERE TRANSACTION_ID=9;
+------------------+
| LATE_OR_LOSS_FEE |
+------------------+
|            29.98 |
|             0.00 |
+------------------+
2 rows in set (0.16 sec)

Notice how differently the MySQL command line client formats its output. While SQL clients from different vendors typically format results differently, the good news is that the data is the same.

ASCII

The ASCII function returns the ASCII character set value (a number between 0 and 255) for a character string containing a single character. For example, the ASCII code for a space is 32, so ASCII(‘ ‘) would return a value of 32.

CHAR (CHR)

The CHAR function (named CHR in Oracle and DB2) returns the character associated with an ASCII value (a number between 0 and 255). For example, the function ASCII(44) returns a comma since the ASCII value for a comma is 44. This function is particularly useful for concatenating characters that either cannot be displayed or would be awkward to handle in SQL. Some of the ASCII characters typically used with this function are listed in the following table. You can use the ASCII function or an ASCII character set table (easily found on the Internet) if you need to know other values.

Some examples follow. Keep in mind that the concatenation operators are not the same for all DBMS implementations (‘+’ for Microsoft SQL Server, ‘||’ for most others).

• Find any movie titles that have a Tab character in them:
  
  Microsoft SQL Server:
  
  SELECT MOVIE_ID FROM MOVIE
   WHERE MOVIE_TITLE LIKE '%'+CHAR(9)+'%';
  MOVIE_ID
  (0 rows affected)
  
  Oracle and DB2:
  
  SELECT MOVIE_ID FROM MOVIE
   WHERE MOVIE_TITLE LIKE '%' || CHR(9) || '%';
  no rows selected

NOTE: You may modify the Tab query to find movies with single quotes in their titles by changing the 9 to 39. You should find Movie ID 3 (Something’s Gotta Give).