Learning SQL

Chapter 7. Data Generation, Conversion, and Manipulation

As I mentioned in the Preface, this book strives to teach generic SQL techniques that can be applied across multiple database servers. This chapter, however, deals with the generation, conversion, and manipulation of string, numeric, and temporal data, and the SQL language does not include commands covering this functionality. Rather, built-in functions are used to facilitate data generation, conversion, and manipulation, and, while the SQL standard does specify some functions, the database vendors often do not comply with the function specifications.

Therefore, my approach for this chapter is to show you some of the common ways in which data is manipulated within SQL statements, and then demonstrate some of the built-in functions implemented by Microsoft SQL Server, Oracle Database, and MySQL. Along with this chapter, I strongly recommend you purchase a reference guide covering all of the functions implemented by your server. If you work with more than one database server, there are several reference guides that cover multiple servers, such as SQL in a Nutshell or SQL Pocket Guide, both from O’Reilly.

Working with String Data

When working with string data, you will be using one of the following character data types:

CHAR

Holds fixed-length, blank-padded strings. MySQL allows CHAR values up to 255 characters in length, Oracle Database permits up to 2,000 characters, and SQL Server allows up to 8,000 characters.

varchar

Holds ...