Home arrow MySQL arrow Advanced Query Writing

Advanced Query Writing

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

  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



Before we move on to Data Manipulation Language in Chapter 7, this chapter is intended to round out your knowledge of SQL queries by covering some advanced topics that were only brushed upon in Chapters 4 and 5. It covers the following topics:

  • Advanced SQL functions, including character, mathematical, and date/time functions
  • A description of how to take advantage of views
  • A description of how to use SQL to generate SQL statements
  • Information on the SQL CASE expression and its use in forming statements that have portions that are only executed under predefined conditions

Advanced SQL Functions

SQL functions were introduced in Chapter 4. The topics that follow describe functions that were not covered in Chapter 4 but which you will find useful. In addition to character and mathematical functions, selected date and time functions are also included. Remember that all SQL functions have a common characteristic in that they return a single value, so they are useful in several places in SQL statements, including the SELECT statement column list and WHERE clause. As a reminder, there are many more implementation-specific functions provided by the various DBMS vendors, so always check the vendor-supplied documentation for more useful functions.

Character Functions

Character functions operate on character data. This topic presents some commonly used functions in addition to those covered in Chapter 4. Except as noted, you will find the function supported by the most popular SQL implementations, including Microsoft SQL Server, Oracle, DB2, and MySQL.


The REPLACE function searches a character string and replaces characters found in a search string with characters listed in a replacement string. Here is the general syntax:

REPLACE(character_string, search_string, replacement_string)

  • character_string is the string to be searched and is most often a table column name, but it can be any expression that yields a character string.
  • search_string is the string of one or more characters to be found in character_string.
  •  replacement_string is the string that replaces any occurrences of search_string that are found in character_string.

Here is an example that replaces all hyphens (dashes) found in a person’s phone number with periods (only the first two rows in the result set are shown):

---------------  ---------------
230-229-8976     230.229.8976
401-617-7297     401.617.7297


The LTRIM function removes any leading (left-hand) spaces in a character string. Note that only leading spaces are removed—embedded and trailing spaces are left in the string. There is no data with leading and/or trailing spaces in the video store database, so here is a general example:

LTRIM (' String with spaces ')
Returns this string: 'String with spaces '


The RTRIM function works like LTRIM, but it removes trailing spaces. If you need to remove both leading and trailing spaces, you can nest LTRIM and RTRIM like this:

RTRIM(LTRIM (' String with spaces '))
Returns this string: 'String with spaces'

NOTE: Oracle provides a convenient function named TRIM that trims both leading and trailing spaces. For other implementations, you can always nest the LTRIM and RTRIM functions and achieve the same result.

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- 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: