SQL VISUAL QUICKSTART GUIDE- P19:SQL (pronounced es-kyoo-el) is the standard programming language for creating, updating, and retrieving information that is stored in databases. With SQL, you can turn your ordinary questions (“Where do our customers live?”) into statements that your database system can understand (SELECT DISTINCT city, state FROM customers;) | Chapter 5 Converting Data Types with CAST In Microsoft SQL Server use to concatenate strings Listing CAST price AS CHAR 8 and Listing CAST sales AS CHAR 8 copies sold of CAST title_name AS CHAR 20 Oracle doesn t allow character conversions to CHAR length if length is shorter than the source string. Instead use SUBSTR to truncate strings see the DBMS Tip in Extracting a Substring with SUBSTRING earlier in this chapter. To run Listing change the CAST expression to CAST sales AS CHAR 8 II copies sold of II SUBSTR title_name 1 20 In MySQL use SIGNED instead of INTEGER for data_type and use CONCATO to concatenate strings. To run Listings and change the CASTO expressions to Listing CAST price AS SIGNED CONCAT CAST price AS CHAR 8 and Listing CONCAT CAST sales AS CHAR 8 copies sold of CAST title_name AS CHAR 20 Oracle treats an empty string as null CAST AS CHAR returns null. See the DBMS Tip in Nulls in Chapter 3. In older PostgreSQL versions to compare a value in a NUMERIC or DECIMAL column with a real floating-point number you must convert the real number to NUMERIC or DECIMAL explicitly. The following statement for example fails in older PostgreSQL versions because the data type of the column price is DECIMAL 5 2 SELECT price FROM titles WHERE price This statement fixes the problem SELECT price FROM titles WHERE price CAST AS DECIMAL DBMSs have additional conversion and formatting functions. Some examples CONVERTO in Microsoft SQL Server and MySQL TO_CHAR TO_DATE TO_TIMESTAMP and TO_NUMBER in Oracle and PostgreSQL and TO_CHAR and TO_DATE in DB2. Search your DBMS documentation for conversion cast or formatting functions. 160 Operators and Functions Evaluating Conditional Values with case The CASE expression and its shorthand equivalents COALESCE and NULLIF let you take actions based on a condition s truth value true false or unknown . The CASE expression s important characteristics are If you ve programmed before you ll .
