SQL basics: String data types (2023)

Data type implementations vary from database to database, but a working knowledge of the SQL specification will always give you a good idea of what's going on. This article breaks down the basic rules of deploying string data types.

SQL data types dictate how a field’s content will be handled, stored, and displayed in a database. SQL92 defines standard data types, which are intended as the blueprint for database manufacturers to build their own data types upon. In a previous article, we introduced a few commonly used data types that fall into four primary types:

• String
• Number
• Datetime
• Interval

This article gives you an overview of how data types are used in a database and then focuses on an explanation of the string data types. This information is useful as a reference page or to get background information about data types for your particular database vendor’s implementation.

Using data types
When you create a table in a database, you define column names and the data type of the content that will reside in those columns. To borrow an example from a previous article:
CREATE TABLE Products
(prod_id INT(16)AUTO_INCREMENT, prod_color VARCHAR(20),
prod_descr VARCHAR(255), prod_size DECIMAL(8,2),
UNIQUE (`prod_id`));

In the above query, the definition, prod_color VARCHAR(20) delivers instructions to create a column named prod_color, with data type VARCHAR of length 20.

Your database interprets a data type by using the descriptor associated with each type. For example, the VARCHAR data type descriptor includes information that distinguishes it as a string data type, containing all string characters, with a variable length. The column’s definition in the database contains other information, such as length that is specific to the data type implementation.

As we mentioned above, each database manufacturer is expected to build its own data types based upon the standard SQL92 definitions. This way, each database can set its own maximum size limits and other attributes to define the data types. Many databases use the same names as the data types listed here, although each implementation may differ slightly. It’s always best to confirm specific data type usage details with your database manufacturer’s documentation.

That being said, read on to learn more about the standard string data types.

Strings
There are two main string data types: characters and bits. String types use the characters available in the SQL_TEXT definition within your database. The SQL92 standard also provides for NATIONAL CHARACTER and NATIONAL CHARACTER VARYING data types that use definable character sets. These types are handled in the same way as CHARACTER and CHARACTER VARYING types.

CHARACTER | CHAR
Usage: CHARACTER(clength) | CHAR(clength)

• The CHARACTER and CHAR keywords are synonymous.
• One distinguishing feature of CHARACTER types is that they are capable of containing the <space> character.
• CHARACTER types contain string characters (from the language set SQL_TEXT) of the constant length, clength.
• <space> characters are concatenated if the value’s length is less than clength. This means CHARACTER fields are of a fixed length.
• You can compare CHARACTER data type fields to other fields of the same type with different lengths, or to CHARACTER VARYING data types.
• Some databases allow comparison to numeric data types.

CHARACTER VARYING | CHAR VARYING | VARCHAR
Usage: CHARACTER VARYING(maxlength) | CHAR VARYING(maxlength) | VARCHAR(maxlength)

• The CHARACTER VARYING, CHAR VARYING, and VARCHAR keywords are synonymous.
• These types contain string characters of a maximum length, maxlength.
• The database records the field’s length as the actual length of the value.
• You can compare these data type fields to other fields of the same type with different maximum lengths, or to CHARACTER data types.

BIT
Usage: BIT(blength)

• This type contains bit characters (1 or 0) of length, blength. For example, if we used BIT(2), a sample value could be “01”.
• Some databases will insert empty bits at the start of the string, while others will concatenate them to meet the fixed length.
• Bit characters are strings, not integers.
• You can compare BIT data type fields to other fields of the same type with different lengths, or to BIT VARYING data types.
• Some databases allow BITS to be compared to CHARACTER or INTEGER types.

BIT VARYING
Usage: BIT VARYING(maxlength)

• This type contains bit characters of a maximum length, maxlength.
• The recorded length is set to the actual length of the value.
• Databases allow comparison to other BIT VARYING data fields, or BIT data fields.

This design flexibility creates one of the snags that must be overcome when migrating from one database to another. In one database, you may have a type called CHAR that has a maximum limit greater than the database you’re moving to. Additionally, types not explicitly defined in the standard can vary wildly, with only convention to facilitate migration.

Builder.com contributor Alexandre Pereira Calsavara discusses one possible solution for protecting data against data type portability problems in his recent article, “A portable alternative for BLOBs.” The SQL standard includes no specification for storing binary data, creating incompatibility issues between database vendors. Software developers must find alternate solutions to overcome these roadblocks.

In the next article in this series, we’ll take a look at the SQL92 number data types, explore what characterizes each one, and look at the rules for database implementation of these types.

Daily Tech Insider Newsletter

Stay up to date on the latest in technology with Daily Tech Insider. We bring you news on industry-leading companies, products, and people, as well as highlighted articles, downloads, and top resources. You’ll receive primers on hot tech topics that will help you stay ahead of the game.

Delivered Weekdays

Sign up today