SQL Convert and SQL Cast — It’s All Back & Forth

Relational databases store information in table cells — in columns that describe aspects of an item and rows which tie together the columns. The SQL Convert and SQL Cast operations both convert data from one type to another (albeit with slightly different syntax). Anything you can do with SQL Convert you can do with SQL Cast. In some database implementations SQL Cast is actually  implemented with SQL Convert. SQL Cast is part of the ANSI-SQL specification; SQL Convert is not, but it is attractive in that it accepts an optional formatting style parameter.

CAST(value AS datatype)

CONVERT(value, type)
CONVERT(value USING charset)

Here we explore some of the ways to use SQL Convert and SQL Cast Into.

Consult your particular database documentation for the exact syntax and argument options because of the variations between implementations. This blog post merely touches the surface of this very deep and very capable set of functions.

Casting and Converting to Native Data Types

When reading data from external sources it’s typical to store them as string data. When calculations are to be performed, say on time or date data, these strings need to be converted. The following shows conversions between several different data types with both SOL Cast and SQL Convert:

SELECT
  CAST("2021-06-10" AS DATE) as 'Date',
  CAST("12:34:56" AS TIME) as 'Time',
  CAST(987654321 AS CHAR) as 'Char',
  CAST(999-1000 AS SIGNED) as 'Negative',
  CAST(1000-999 AS SIGNED) as 'Positive' ;

SELECT
  CONVERT("2021-06-10", DATE) as 'Date',
  CONVERT("12:34:56", TIME) as 'Time',
  CONVERT(987654321, CHAR) as 'Char',
  CONVERT(999-1000, SIGNED) as 'Negative',
  CONVERT(1000-999, SIGNED) as 'Positive' ;

Date	Time	Char	Negative	Positive
2021-06-10	12:34:56	987654321	-1	1

Data types typically supported include BINARY, CHAR, DATE, DATETIME, DECIMAL, NCHAR, SIGNED, TIME, and UNSIGNED.

Casting Shows Underlying Structure

All data are stored as strings of bits. How we interpret those bits are what give meaning to them, hence the importance of choosing proper data types to represent the data. For example, the string of bits which represent negative one, when viewed as the data type SIGNED, appears as a huge number when viewed as an UNSIGNED number.

SELECT
  -1,
  CAST( -1 AS UNSIGNED ),
  CAST(CAST( -1 AS UNSIGNED ) AS SIGNED);

-1	CAST( -1 AS UNSIGNED )	CAST(CAST( -1 AS UNSIGNED ) AS SIGNED)
-1	18446744073709552000	-1

Casting and Converting Between String Character Sets

We can move between character sets, useful in internationalization and localization. The following example shows SQL Convert and the resulting differences in length between single-byte and multiple-byte strings:

SET @something = 'piglet' ;
SET @latin1 = CONVERT( @something USING latin1 ) ;
SET @ucs2 = CONVERT( @something USING ucs2 ) ;
SELECT
    CHAR_LENGTH( @latin1 ),
    LENGTH( @latin1 ),
    CHAR_LENGTH( @ucs2 ),
    LENGTH( @ucs2 );

CHAR_LENGTH( @latin1 )	LENGTH( @latin1 )	CHAR_LENGTH( @ucs2 )	LENGTH( @ucs2 )
6	6	6	12

This becomes important when comparing strings in different character sets, which will raise an error:

SET
 @s1 = _latin1 'piglet',
 @s2 = _latin2 'piglet';
SELECT @s1 = @s2;

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin2_general_ci,IMPLICIT) for operation ‘=’

A conversion fixes this type of issue:

SELECT @s1 = CONVERT(@s2 USING latin1);

@s1 = CONVERT(@s2 USING latin1)

1

Some operations don’t work on character sets. In that case, use the SQL Binary function first and then apply the transformations with the desired character set. 

@whatever = BINARY 'CamelCaseVariable';
SELECT LOWER(CONVERT(@whatever USING utf8mb4));

LOWER(CONVERT(@whatever USING utf8mb4))

camelcasevariable

Implicit Conversions

The explicit use of a conversion function is necessary when SQL’s implicit, on-the-fly data conversions don’t cover your use case: For example, SQL implicitly converts between strings and decimal, hexadecimal, and binary numbering systems:

SELECT
  '51'+50,
  X'41'+36,
  B'1100001'+4;

’51’+50	X’41’+36	B’1100001’+4
101	101	101

Numbers used in a string context are also implicitly converted, into strings.

SELECT CONCAT('I have ', 2, ' eyes.');

CONCAT(‘I have ‘, 2, ‘ eyes.’)

I have 2 eyes.

Conclusion

We’ve covered the syntax of the related SQL Cast and SQL Convert functions, and their use in putting data into the proper types, as in migrating strings representing dates and times into their SQL data types so the database can appropriately search, sort, and manipulate the data.

Start Learning

To learn more about SQL, check out other SQL blog posts and enroll in our SQL Nanodegree program.

SQL Sample Table Code

The following will build and populate the sample data referred to in this blog post. Test SQL interactively at  SQL Fiddle or ExtendsClass.

(All SQL is included above in this post.)