SQL basics: String data types (2023)

by shelleydoll in Data Management


SQL basics: String data types

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:
(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.

(Video) 3. What are SQL Data Types and Sizes? VARCHAR, INT, FLOAT, SMALLINT and many more covered in detail.

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.

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.

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.

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.

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.

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.
Got an idea for our SQL series?

Please post your comments, questions, or feedback in the discussion below, or send our editors an e-mail if you have a topic idea for the SQL basics series.

String theory
Database manufacturers build upon these foundation data types to create the data types that you actually implement. For strings, this can include types of the same name, such as CHAR or BIT, or be extended to include TEXT, SMALL TEXT, and other types including string data.

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.

(Video) Learning MySQL - String and Binary Datatypes

SQL basics: String data types (2)

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

By shelleydoll

  • Account Information

    Contact shelleydoll

    (Video) Advanced SQL — Chapter #03 — Video #14 — String data types (char/varchar/text), type numeric(s,p)
  • |
  • See all of shelleydoll's content
  • Data Management

Editor's Picks

  • TechRepublic Premium

    TechRepublic Premium editorial calendar: IT policies, checklists, toolkits and research for download

    TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.

  • Tech & Work

    The best payroll software for your small business in 2023

    Looking for the best payroll software for your small business? Check out our top picks for 2023 and read our in-depth analysis.

  • Security

    Top cybersecurity threats for 2023

    Next year, cybercriminals will be as busy as ever. Are IT departments ready?

  • Cloud

    Salesforce supercharges its tech stack with new integrations for Slack, Tableau

    The company, which for several years has been on a buying spree for best-of-breed products, is integrating platforms to generate synergies for speed, insights and collaboration.

    (Video) Advanced SQL Tutorial | String Functions + Use Cases

  • Software

    The best applicant tracking systems for 2023

    Organize a number of different applicants using an ATS to cut down on the amount of unnecessary time spent finding the right candidate.

  • Software

    108 Excel tips every user should master

    Whether you are a Microsoft Excel beginner or an advanced user, you'll benefit from these step-by-step tutorials.

SQL basics: String data types (10)

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.

(Video) SQL Server-7(SQL Datatypes-Numeric ,Date and Time,String,Binary ,Miscellaneous Data Types)

Delivered Weekdays

Sign up today


Does SQL have string data type? ›

String Data Types

Equal to VARCHAR(), but stores binary byte strings. The size parameter specifies the maximum column length in bytes.

What type of data type is string? ›

A string is generally considered a data type and is often implemented as an array data structure of bytes (or words) that stores a sequence of elements, typically characters, using some character encoding.

How do you declare a string type in SQL? ›

SQL Declare variable string

To declare a string variable, use the DECLARE keyword, then type the @variable_name and variable type: char, varchar. To assign a value to a variable, use the keyword SET. The CHAR is a data type with fixed length and loads empty spaces.

What is a string data type example? ›

A string is a data type used in programming, such as an integer and floating point unit, but is used to represent text rather than numbers. It is comprised of a set of characters that can also contain spaces and numbers. For example, the word "hamburger" and the phrase "I ate 3 hamburgers" are both strings.

What is string in SQL example? ›

A string function is a function that takes a string value as an input regardless of the data type of the returned value. In SQL Server, there are many built-in string functions that can be used by developers.

Is SQL VARCHAR a string? ›

VARCHAR is a variable length string data type, so it holds only the characters you assign to it. VARCHAR takes up 1 byte per character, + 2 bytes to hold length information.

What is an SQL string? ›

In sql, string data types are used to store any kind of data in the table. In string data types, we have an option to allow users to store either the fixed length of characters or huge length data based on their requirements.

What is a string in database? ›

STRING is a database of known and predicted protein-protein interactions. The interactions include direct (physical) and indirect (functional) associations; they stem from computational prediction, from knowledge transfer between organisms, and from interactions aggregated from other (primary) databases.

What is the purpose of string data type? ›

Character strings are the most commonly used data types. They can hold any sequence of letters, digits, punctuation, and other valid characters. Typical character strings are names, descriptions, and mailing addresses.

What is the basic rule to declare a string? ›

Declaring a string is as simple as declaring a one-dimensional array. Below is the basic syntax for declaring a string. char str_name[size]; In the above syntax str_name is any name given to the string variable and size is used to define the length of the string, i.e the number of characters strings will store.

What is the syntax of string? ›

A syntax string is a group of characters and syntax codes ( # , * , & , @ , ~ , ? , ( , ) , % , and ^ ) enclosed in single quotation marks ( ' ). Syntax strings can be used to specifying a field syntax, such as postal code or telephone number.

How do I pass a string in SQL? ›

To pass string parameters in an SQL statement, single quotes (' ') must be part of the query. Example for Single quotes being part of the query.

What are the types of string? ›

  • Twine.
  • Yarn.
  • Bowstring.
  • Drawstring.
  • Pullstring.
  • Shoestrings.
  • Strings on musical instruments.
  • Tennis strings.

What is the varchar in SQL? ›

So what is varchar in SQL? As the name suggests, varchar means character data that is varying. Also known as Variable Character, it is an indeterminate length string data type. It can hold numbers, letters and special characters.

Is string a simple data type? ›

The string data type is a non-primitive data type but it is predefined in java, some people also call it a special ninth primitive data type. This solves the case where a char cannot store multiple characters, a string data type is used to store the sequence of characters.

Is SQL query string? ›

Even though the SQL value is a string, SQL Server executes the string as if it is SQL code. Transact SQL, or T-SQL, provides you with the language to create the dynamic code. It retrieves data from a database and adds data into your server's tables.

What is different between a string and a VARCHAR? ›

Use varchar if you want to have a length constraint. Use string if you don't want to restrict the length of the text.

What is the difference between VARCHAR and string? ›

Varchar datatype can be used when we expect the data values in a column to be of variable length.
Differences between Char & Varchar.
We can use char datatype when we know the length of the string.We can use it when we are not sure of the length of the string.
6 more rows
Oct 1, 2021

What is CHAR vs VARCHAR vs VARCHAR2? ›

Differences: CHAR vs VARCHAR vs VARCHAR2

CHAR does not need a size specified and has a default of 1. A size needs to be specified with VARCHAR/VARCHAR2 columns. CHAR will pad spaces to the right of strings to match the length of the column, while VARCHAR/VARCHAR2 does not.

How to store string in SQL? ›

To store strings in a database, use OpenROAD to add them to a table. OpenROAD automatically creates a special string storage table, called ii_stored_strings.

Why string is not a data type? ›

The string data type is a non-primitive data type but it is predefined in java, some people also call it a special ninth primitive data type. This solves the case where a char cannot store multiple characters, a string data type is used to store the sequence of characters.


1. SQL 033 Data Types, Character String Data, VARCHAR
2. SQL 034 Data Types, Character String Data, LONG VARCHAR
3. SQL 032 Data Types, Character String Data, CHAR or CHARACTER
4. SQL 036 Data Types, Character String Data, CLOB or Character Large OBject
5. SQL Server DATETIME, DATE and TIME data types | Manipulating Dates, times and Strings | SQL basics
(Database Doodle)
6. SQL: Data Types; Creating Tables and Inserting Rows
(Dave Sullivan)
Top Articles
Latest Posts
Article information

Author: Moshe Kshlerin

Last Updated: 02/19/2023

Views: 5757

Rating: 4.7 / 5 (77 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Moshe Kshlerin

Birthday: 1994-01-25

Address: Suite 609 315 Lupita Unions, Ronnieburgh, MI 62697

Phone: +2424755286529

Job: District Education Designer

Hobby: Yoga, Gunsmithing, Singing, 3D printing, Nordic skating, Soapmaking, Juggling

Introduction: My name is Moshe Kshlerin, I am a gleaming, attractive, outstanding, pleasant, delightful, outstanding, famous person who loves writing and wants to share my knowledge and understanding with you.