5.5. CONVERSION Functions

Sometimes data in a table is stored in a particular data type, but you need to have the data in another data type. For example, let us suppose that columnA of TableA is of character data type, but you need to use this column as a numeric column in order to do some mathematical operations. Similarly, there are times where you have a table with numeric data types and you need characters. What do you do? SQL Server provides three functions for converting data types--CAST, CONVERT, and STR. In the following subsections, we discuss each of these functions.

5.5.1. The CAST Function

The CAST function is a very useful SQL Server function that allows you to change a data type of a column. The CAST result can then be used for:

• Concatenating strings

• Joining columns that were not envisioned as related

• Performing unions of tables (unions are discussed in Chapter 7)

• Performing mathematical operations on columns that were defined as character but which actually contain numbers that need to be calculated.

Some conversions are automatic and implicit, so using CAST is not necessary. For example, converting between numbers with types INT, SMALLINT, TINYINT, FLOAT, NUMERIC, and so on is done automatically and implicitly as long as an overflow does not occur. But, converting numbers with decimal places to integer data types truncates values to the right of the decimal place without a warning, so you should use CAST if a loss of precision is possible.

The general form of the syntax for the CAST function is:

    CAST (original_expression AS desired_datatype)

To illustrate the CAST function, we will use the Employee table that we created earlier in this chapter. In this table, names was defined as a NVARCHAR column, wage was defined as a SMALLMONEY column, and hours was defined as a SMALLINT column. We will use CAST to change the display of the hours column to a character column so that we can concatenate a string to it, as shown in the following query:

    SELECT names, wage, hours = CAST(hours AS CHAR(2)) + ' hours worked per week'
    FROM   Employee

This query will give us:

    names                wage         hours
    -------------------- ------------ ------------------------
    Sumon Bagui          10.0000      40 hours worked per week
    Sudip Bagui          15.0000      30 hours worked per week
    Priyashi Saha        18.0000      NULL
    Ed Evans             NULL         10 hours worked per week
    Genny George         20.0000      40 hours worked per week

    (5 row(s) affected)

CAST will truncate the value or column if the character length is smaller than the size required for full display.

CAST is a subset of the CONVERT function, and was added to SQL Server to comply with ANSI-92 specifications.

5.5.2. The STR Function

STR is a specialized conversion function that always converts from a number (for example, float or numeric) to a character data type. It allows you to explicitly specify the length and number of decimal places that should be formatted for the character string.

The general form of the syntax for the STR function is:

    STR(float_expression, character_length, number_of_decimal_places)

character_length and number_of_decimal_places are optional arguments.

character_length must include room for a decimal place and a negative sign. STR rounds a value to the number of decimal places requested.

We will illustrate the use of the STR function using the Employee table that we created earlier in this chapter. In this table, the hours column is a SMALLINT column. To format it to two decimal places, we can use STR. Note that we have to make the character length 5 in this case in order to accommodate the .00 (the decimal point and zeros). Following is the query showing this:

    SELECT names, wage, hours = STR(hours, 5, 2)
    FROM   Employee

which produces:

    names                wage                  hours
    -------------------- --------------------- -----
    Sumon Bagui          10.00                 40.00
    Sudip Bagui          15.00                 30.00
    Priyashi Saha        18.00                 NULL
    Ed Evans             NULL                  10.00
    Genny George         20.00                 40.00

    (5 row(s) affected)

5.5.3. The CONVERT Function

Just like the CAST function, the CONVERT function is also used to explicitly convert to a given data type. But, the CONVERT function has additional limited formatting capabilities.

The general syntax for the CONVERT function is:

    CONVERT(desired_datatype[(length)], original_expression [, style])

CONVERT has an optional third parameter, style, which is used for formatting. If style is not specified, it will use the default style. Because the CONVERT function has formatting capabilities, it is widely used when displaying dates in a particular format. Examples of the use of the CONVERT function are presented in the section, "Default Date Formats and Changing Date Formats" later in this chapter.