Home arrow MySQL arrow Page 2 - Advanced Query Writing

More Character Functions - MySQL

This article, the first of two parts, covers some advanced topics concerning SQL queries and functions. It is excerpted from chapter six of the book SQL DeMYSTiFied, written by Andrew Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249).

TABLE OF CONTENTS:
  1. Advanced Query Writing
  2. More Character Functions
  3. Mathematical Functions
  4. Date and Time Functions
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 38
March 02, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.

ASCII Value

Character

9

Tab

10

Line feed

13

Carriage return

39

Single quote

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).



 
 
>>> More MySQL Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: