SQL Character Data Types
Introduction
Character data types store text: names, addresses, descriptions, codes, email addresses, phone numbers, URLs, labels, and documents. Selecting a character type looks simple until an application must support international scripts, enforce realistic lengths, compare values consistently, search efficiently, and move data between databases. At that point, the distinction among CHAR, VARCHAR, Unicode variants, and large-text types becomes part of the data model rather than a formatting preference.
A strong design begins by asking whether a value is truly textual. A postal code may contain leading zeros and letters, so text is appropriate. A birth date, quantity, or boolean has native semantics and should normally use its corresponding type. Once text is justified, the schema must define variable or fixed length, maximum size, character set, collation, NULL behavior, uniqueness, indexing, and security requirements.
What Is Character Data?
Character data is a sequence of characters interpreted as text rather than a number, date, binary object, or other specialized value. Text can contain letters, digits, spaces, punctuation, symbols, and characters from many writing systems. The apparent content does not determine the type by itself. A phone number contains digits but is not used for arithmetic; a product quantity is numeric even if a user enters it through a text box.
SQL character columns preserve textual identity and provide operations such as comparison, concatenation, pattern matching, case conversion, trimming, and substring extraction. Their exact behavior depends on length rules, encoding, and collation.
Main Character Type Families
| Family | Typical types | Purpose |
|---|---|---|
| Fixed length | CHAR(n), NCHAR(n) | Values with a stable declared width |
| Variable length | VARCHAR(n), NVARCHAR(n) | Most short and medium text |
| Large text | TEXT, CLOB, product-specific MAX types | Documents and long free-form content |
Names differ among products, and Unicode support is not always tied to a separate type. PostgreSQL text types use the database encoding, while SQL Server historically distinguishes VARCHAR and NVARCHAR. The shared concept is more portable than any one declaration.
CHAR and Fixed-Length Text
CHAR(n) declares fixed-length character data. A database commonly pads shorter stored values with spaces to the declared length, though retrieval and comparison details vary. It can suit genuinely fixed-width domains such as two-letter region codes, three-letter currency codes, or standard identifiers whose exact width is part of the specification.
CREATE TABLE currencies (
currency_code CHAR(3) PRIMARY KEY,
currency_name VARCHAR(80) NOT NULL
);
Fixed length should describe the domain, not an assumption that fixed storage is always faster. Human names, addresses, and comments vary naturally and should not be padded to an arbitrary width.
VARCHAR and Variable-Length Text
VARCHAR(n) stores variable-length text up to a declared limit. It is the usual choice for names, email addresses, product labels, paths, and other bounded values whose lengths differ by row. The database stores the actual content plus implementation metadata rather than filling every value to the maximum.
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
display_name VARCHAR(150) NOT NULL,
email_address VARCHAR(320),
phone_number VARCHAR(32)
);
The limit is a data rule, not a reservation of that much space for every row. It communicates capacity and can reject unexpectedly large input before it spreads through the system.
CHAR Versus VARCHAR
| Concern | CHAR(n) | VARCHAR(n) |
|---|---|---|
| Length model | Fixed declared length | Variable up to a maximum |
| Padding | Usually space padded | Normally no fixed padding |
| Best fit | Stable-width standardized codes | Most ordinary text |
| Storage | Based on fixed width | Based mainly on actual length |
| Main risk | Trailing-space surprises and waste | Poorly chosen maximum length |
A code is not automatically fixed length. If valid values have different lengths or may evolve, VARCHAR is more honest. Conversely, using VARCHAR(3) for an ISO currency code works but does not express the stable three-character shape as directly as CHAR(3).
Choosing a VARCHAR Length
The maximum should come from a documented domain requirement, external standard, or measured operational need. A limit that is too small truncates or rejects legitimate data. A huge default such as VARCHAR(4000) for every field weakens validation and can affect indexes, row layout, memory grants, client buffers, and schema communication.
Do not confuse the current longest sample with the maximum valid value. Names and addresses vary across cultures, and external identifiers can change formats. Select a defensible boundary, validate it at application and database layers, and review it during integrations.
TEXT, CLOB, and Large Character Values
Large-text types store content that does not fit a practical short-column limit: article bodies, legal documents, logs, templates, or substantial user notes. PostgreSQL commonly uses TEXT; MySQL provides multiple TEXT sizes; SQL Server uses VARCHAR(MAX) or NVARCHAR(MAX); Oracle offers CLOB for large character objects.
Large types may have different storage, indexing, function, comparison, or driver behavior. They are not automatically faster or more flexible for ordinary values. A status code should remain a constrained short column, while a full document belongs in a large-text type when storing it in the database is appropriate.
VARCHAR Versus TEXT
In PostgreSQL, bounded VARCHAR and TEXT have similar storage and performance characteristics for many workloads; the declared limit is the primary semantic difference. Other products treat large text more distinctly. MySQL text families have size and indexing rules, and older database versions may restrict operations on legacy large-object types.
Choose from the target database's behavior and the domain's length needs, not from a universal performance myth. If a short business identifier has a meaningful maximum, a bounded type provides useful validation. If content is genuinely long and variable, use the supported large-text type deliberately.
Unicode and International Text
Unicode provides a common system for representing writing systems, symbols, and emoji. Modern applications should assume that names, addresses, and user-generated text can require Unicode even when the first release targets one language. Losing characters during storage is a data-integrity defect, not merely a display issue.
SQL Server commonly uses NCHAR and NVARCHAR for Unicode data, with Unicode literals traditionally prefixed by N. PostgreSQL uses the database encoding for CHAR, VARCHAR, and TEXT. MySQL Unicode depends on the selected character set; full Unicode including emoji typically requires the appropriate four-byte UTF-8 character set rather than an older limited variant.
Character Sets and Encoding
A character set defines the available characters, while an encoding maps those characters to bytes. UTF-8 uses a variable number of bytes per character, so character count and byte count are not interchangeable. A value with ten characters can occupy more than ten bytes.
Length semantics differ among products and types. Some declarations count characters, some historical settings can use byte semantics, and index limits may be measured in bytes. Verify database, table, column, connection, driver, and file encodings. Correct storage can still fail if a client sends bytes using the wrong encoding.
Collation Controls Comparison and Order
Collation defines how text is compared and sorted. It can control case sensitivity, accent sensitivity, linguistic order, and equivalence rules. Under one collation, A and a compare as equal; under another, they are distinct. An accent-insensitive collation may treat accented and unaccented forms as equivalent for comparison.
Collation affects WHERE, ORDER BY, joins, grouping, distinctness, and uniqueness. It can be configured at database, table, column, or expression level depending on the product. A uniqueness rule for email or usernames must be designed with the same comparison semantics used during login and lookup.
Binary and Linguistic Collations
A binary collation compares encoded values in a byte- or code-point-oriented order. It is deterministic and useful for machine tokens or cases that require exact distinctions, but it rarely produces natural dictionary order for human language. Linguistic collations apply locale-aware rules and may handle case, accents, and composed characters more naturally.
There is no single correct collation for every column. Product names shown to people, security tokens, usernames, and standardized codes can have different requirements. Record the decision rather than inheriting a database default accidentally.
Character Literals and Parameterization
SQL string literals use single quotes. Double quotes usually delimit identifiers under standard SQL behavior. An apostrophe inside a literal is escaped by doubling it, but applications should not build SQL by manually escaping user input.
SELECT 'O''Brien' AS family_name;
SELECT customer_id
FROM customers
WHERE email_address = ?;
Parameterized queries separate SQL structure from data, reduce injection risk, preserve type information, and improve plan reuse. The parameter's character type, length, encoding, and collation compatibility still matter.
Empty String Versus NULL
An empty string is a known value containing zero characters. NULL represents missing or unknown data. They should not be treated interchangeably unless a domain explicitly defines that equivalence. A blank middle name and an unknown middle name may carry different meaning.
Oracle traditionally treats an empty character string as NULL, unlike many other databases. Cross-database applications must test this behavior. Avoid meaningless empty-string defaults because they can hide missing required input and complicate uniqueness and reporting.
CHAR Padding and Trailing Spaces
A short value assigned to CHAR(n) is generally padded with spaces. Some comparisons ignore trailing spaces while functions or binary comparisons may expose them. Driver behavior can also trim or preserve padding. These differences create confusing equality and length results when fixed-length types are used casually.
VARCHAR avoids fixed padding but can still contain intentional or accidental leading and trailing spaces. Decide whether surrounding whitespace is meaningful, normalize at a controlled boundary, and test comparison behavior in the selected database.
String Length Functions
Function names and semantics vary. PostgreSQL length() returns character length for text. MySQL distinguishes CHAR_LENGTH() from byte-oriented LENGTH(). SQL Server LEN() commonly excludes trailing spaces, while DATALENGTH() reports storage bytes.
Choose the function that measures the business rule. A user-facing maximum usually concerns characters, while an index or payload limit may concern bytes. Tests should include multibyte characters and trailing spaces.
Common String Functions
Databases provide functions such as UPPER, LOWER, TRIM, SUBSTRING, REPLACE, and CONCAT. They support cleaning, presentation, extraction, and matching, but function syntax and NULL behavior can differ by product.
SELECT TRIM(display_name),
LOWER(email_address),
SUBSTRING(product_code FROM 1 FOR 3)
FROM customers;
A function applied during every query may indicate that data should be normalized on write or that a generated column or functional index is needed. Do not destroy meaningful case or spacing merely for convenience.
LIKE and Pattern Matching
LIKE supports patterns in which percent matches any sequence and underscore matches one character. A prefix pattern such as 'ABC%' can often use an appropriate index, while a contains pattern such as '%ABC%' usually cannot use a normal B-tree index efficiently.
SELECT product_id, product_name
FROM products
WHERE product_name LIKE 'Desk%';
Case sensitivity follows the database and collation. PostgreSQL also provides ILIKE for case-insensitive matching. Escape wildcard characters when they must be treated literally.
Full-Text Search
Repeated contains searches across articles or documents should not automatically rely on LIKE '%term%'. Full-text search systems tokenize language, build specialized indexes, and can provide ranking, stemming, and phrase behavior. Their configuration must match language and search requirements.
Use ordinary equality or prefix matching for codes and short identifiers, and full-text facilities for document discovery. These are different problems even though both operate on character data.
Indexes on Character Columns
Indexes can support equality, prefix, ordering, joins, and uniqueness on character columns. Wider values and multibyte encodings increase index size, reducing the number of entries per page. Some products limit indexed length or offer prefix indexes for long columns.
Case-insensitive lookup may use a compatible case-insensitive collation, a normalized stored column, or a functional index such as an index on LOWER(email_address). The query expression must match the index design. Applying arbitrary functions to the indexed column can make a predicate non-sargable.
Names and Human Identity
Human names are variable, international, and structurally diverse. Do not force every person into assumptions about first, middle, and last names unless the business process truly requires those components. A display-name field can coexist with structured fields needed for a particular legal or mailing context.
Names require Unicode, realistic lengths, and preservation of meaningful spaces, punctuation, accents, and case. Aggressive normalization can damage identity data and should not be used merely to simplify search.
Email Addresses, Phones, and Postal Codes
Email addresses belong in character columns. Practical validation should reject obvious errors and confirm ownership where necessary rather than attempting an unmaintainable complete grammar in a single database expression. Case handling requires a documented application policy.
Phone numbers and postal codes are also text. They may contain plus signs, leading zeros, extensions, spaces, letters, or punctuation and are not arithmetic quantities. Store a canonical searchable representation alongside presentation or source formatting when both are needed.
Codes, Statuses, and Controlled Values
Short codes can use CHAR or VARCHAR based on whether their length is truly fixed. A CHECK constraint works for a small stable set, while a lookup table supports metadata and evolving values.
status_code VARCHAR(20) NOT NULL
CHECK (status_code IN ('PENDING', 'PAID', 'CANCELLED'))
A one-character flag such as 'Y' and 'N' is still a controlled domain and needs a constraint. Use a native boolean when that better expresses the model.
URLs, UUIDs, and Hashes
URLs are variable text with potentially substantial length. UUIDs can be stored as text, but a database's native UUID type often validates format, uses less space, and indexes more efficiently. Fixed-length encoded hashes may fit CHAR, while raw hashes can use binary types more compactly.
The visible shape of a value does not prove that character storage is best. Prefer a native semantic type when it supplies validation, storage, and operators aligned with the domain.
Passwords, Tokens, and Sensitive Text
Passwords must not be stored as plaintext or encrypted for later recovery. Store a salted output from a suitable password-hashing algorithm through a well-maintained authentication library. Allocate enough length for the algorithm's encoded format and support future algorithm upgrades.
Security tokens, API keys, and verification codes can be case-sensitive. Do not lowercase, trim, or compare them under an insensitive collation unless the protocol explicitly permits it. Logs and exports must avoid exposing secrets even when database access is restricted.
Constraints, Keys, and Relationships
Character columns can use NOT NULL, CHECK, UNIQUE, primary keys, and foreign keys. A character natural key may be appropriate for a stable standardized code, but mutable or long business text often makes a poor primary key. Surrogate integer or native UUID keys can isolate relationships from changing labels.
Foreign-key columns must use compatible types, lengths, encodings, and collations. A mismatch can prevent constraint creation, cause conversions, or make joins inefficient. The same logical identifier should have the same declaration throughout the schema.
Sorting, Grouping, and Uniqueness
ORDER BY uses collation rules, so results are not guaranteed to match one universal dictionary order. GROUP BY, DISTINCT, and unique constraints also depend on textual equivalence. Case-insensitive collation can group or reject values that differ only by case.
When deterministic pagination is required, add a unique tie-breaker after the human-readable sort key. When uniqueness must be exact for a machine identifier, select exact collation semantics rather than relying on the database default.
Whitespace and Normalization
Leading and trailing spaces are often accidental in codes and contact fields, while internal spaces can be meaningful in names and addresses. Normalize only according to domain rules. Replacing every sequence of spaces or stripping punctuation globally can corrupt valid data.
Unicode normalization matters because visually similar text can have different code-point sequences. Homoglyphs can also look alike while representing different characters. Security-sensitive identifiers need an explicit normalization and comparison policy; ordinary display text should preserve legitimate user content.
Locale and Case Folding
Uppercase and lowercase conversion is locale-sensitive. The well-known Turkish dotted and dotless I behavior demonstrates why English-oriented assumptions do not hold everywhere. A collation or case-folding method appropriate for one language may be wrong for another.
Use locale-aware behavior for human text and protocol-defined behavior for machine identifiers. Test with actual supported languages rather than validating only ASCII examples.
Do Not Store Native Values as Text
Dates stored as strings lose reliable date validation, arithmetic, and chronological ordering when formats vary. Numbers stored as text sort lexically and require parsing for every calculation. Boolean words invite inconsistent spellings and casing. Native types express semantics and allow the database to enforce them.
Character storage is justified when the apparent number is an identifier, such as a postal code, or when the original textual form itself must be retained. Otherwise, use the native date, numeric, boolean, UUID, JSON, or binary type.
Avoid Comma-Separated Values
Storing several values in one character column violates relational structure, makes validation difficult, and prevents efficient keys and joins. A list of product tags or phone numbers should normally be represented by related rows. Structured JSON is different but still requires a deliberate reason and product support.
Do not use a large text column as an escape from data modeling. If elements need independent search, constraints, or relationships, model them independently.
Database-Specific Character Types
PostgreSQL provides CHAR, VARCHAR, and TEXT under the database encoding and can move large values through its TOAST storage mechanism. MySQL provides CHAR, VARCHAR, and several TEXT families whose limits and index behavior depend on character set and row format.
SQL Server distinguishes VARCHAR and NVARCHAR and uses MAX forms for large values. Oracle commonly favors VARCHAR2 over VARCHAR and uses CLOB for large text. SQLite has dynamic typing and TEXT affinity rather than the same enforcement model as strongly typed engines. Portability requires mapping concepts and testing limits.
Imports, Exports, and File Boundaries
Character data frequently enters through CSV files, spreadsheets, message queues, and third-party feeds. Each boundary needs an explicit encoding, delimiter, quoting rule, line-ending policy, NULL representation, and maximum field length. A file can be valid UTF-8 yet still fail because a parser assumes a different delimiter or treats an empty field as NULL. Byte-order marks and inconsistent line endings also deserve tests when integrations span operating systems and tools.
Do not silently truncate oversized values during bulk loading. Route rejected rows to an auditable error process that records the source row and reason without exposing sensitive content unnecessarily. Preserve the original source value when transformations such as trimming, case normalization, or character replacement must be reviewed later. If a feed claims a fixed width, verify both character and byte semantics before mapping it to CHAR.
Exports require equal care. A database may store line breaks, delimiters, quotation marks, and Unicode correctly, but an improperly configured writer can create an ambiguous or corrupted file. Round-trip tests should include those characters and should compare decoded values rather than only file sizes.
Data Quality and Operational Monitoring
Length constraints prevent some invalid input, but production monitoring should reveal patterns the schema cannot express completely. Useful measures include rejected rows, unexpected NULL or empty-string rates, leading and trailing whitespace, invalid controlled codes, replacement characters caused by decoding failures, and sudden changes in average or maximum length. A new upstream format often appears first as a distribution change before it becomes an outage.
Monitor uniqueness conflicts under the production collation. A system migrating to case-insensitive comparison may discover existing usernames that differ only by case, while Unicode normalization can reveal visually identical values with different sequences. These conflicts need a business resolution process rather than an arbitrary winner.
Large text deserves size and growth monitoring because documents can dominate storage, backups, replication, and search indexes. Track both row counts and content volume. Retention, compression, archival, and redaction rules should be based on the sensitivity and lifecycle of the content, not simply on the fact that the database can hold it.
Validation Ownership Across Layers
Browser limits improve usability, service validation provides domain-specific feedback, and database constraints protect data regardless of its entry path. These layers should implement one compatible contract rather than contradictory rules. The database remains the final integrity boundary, but it cannot replace ownership verification, authorization, or context-sensitive security checks.
Publish shared examples for valid, invalid, and boundary values. Include multilingual text, emoji, whitespace, apostrophes, maximum lengths, and case variants. When the contract changes, update schema, API specifications, clients, imports, and monitoring together so an apparently harmless length increase does not fail elsewhere.
Real-World Customer Schema
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
display_name VARCHAR(200) NOT NULL,
email_address VARCHAR(320),
phone_e164 VARCHAR(20),
postal_code VARCHAR(20),
country_code CHAR(2) NOT NULL,
notes TEXT,
UNIQUE (email_address)
);
The example uses variable Unicode-capable text according to the target database, fixed country codes, and large text only for notes. Production design must select email comparison semantics and decide whether NULL emails or repeated NULLs are allowed.
Real-World Product Schema
CREATE TABLE products (
product_id BIGINT PRIMARY KEY,
product_code VARCHAR(40) NOT NULL,
product_name VARCHAR(200) NOT NULL,
description TEXT,
status_code VARCHAR(20) NOT NULL,
UNIQUE (product_code),
CHECK (status_code IN ('DRAFT', 'ACTIVE', 'RETIRED'))
);
The code limit reflects an identifier contract, the name is bounded display text, and the description can be long. Status is constrained separately because length alone cannot enforce valid states.
Character Data Security
Character input is a common carrier for SQL injection, cross-site scripting, log forging, and malicious markup. Parameterized SQL protects query structure, but output encoding, authorization, file handling, and context-specific validation remain necessary. Database type and length limits reduce risk but do not sanitize content.
Retain original text only when needed, limit access to sensitive columns, and avoid logging secrets or unnecessary personal data. Validation should reject content that violates the field's domain rather than attempting one generic cleaning operation for every context.
Migration and Type Changes
Before reducing length, profile character counts and byte counts, including trailing spaces and multibyte characters. Before changing collation, identify values that will become equal under the new rules because a unique index may then fail. Converting from non-Unicode to Unicode requires validating the source encoding so corrupted bytes are not merely copied into a larger type.
CHAR-to-VARCHAR migrations should review padding and application assumptions. Large-text migrations must test drivers, indexes, replication, and backup behavior. Always update ORM metadata and API limits with the schema.
Common Character-Type Mistakes
- Using
CHARfor naturally variable names and addresses. - Declaring every column as
VARCHAR(255)without a domain reason. - Using a large-text type for short codes merely because it has no small limit.
- Storing phone numbers or postal codes as integers.
- Storing dates, quantities, and booleans as text.
- Assuming
VARCHARmeans ASCII or that Unicode always requires the same type. - Ignoring byte length, collation, trailing spaces, or locale behavior.
- Allowing silent truncation instead of reporting invalid input.
- Combining several relational values into one comma-separated string.
- Building SQL by concatenating character input.
- Case-normalizing security tokens without protocol permission.
- Expecting one
ORDER BYcollation to match every language.
Character Type Selection Checklist
- Confirm that the value is textual rather than a native date, number, boolean, UUID, JSON, or binary value.
- Determine whether valid length is truly fixed or variable.
- Define a realistic maximum using standards and business requirements.
- Select a large-text type only for genuinely long content.
- Choose character set and encoding for all required scripts and symbols.
- Define collation, case, accent, and locale behavior.
- Specify NULL, whitespace, normalization, and default rules.
- Add domain, uniqueness, key, and relationship constraints.
- Plan equality, prefix, contains, and full-text search separately.
- Test drivers, APIs, imports, exports, and database migrations.
Testing Character Columns
Test empty strings, NULLs, minimum and maximum valid lengths, one character beyond the limit, leading and trailing spaces, apostrophes, wildcard characters, mixed case, accents, multiple scripts, emoji, and characters requiring several bytes. Include visually similar Unicode values where identifiers or security are involved.
Verify equality, uniqueness, ordering, grouping, joins, prefix searches, contains searches, and index plans under the production collation. Round-trip values through the actual driver, API serializer, browser, file import, and export process. A database-only test cannot detect encoding loss in another layer.
Interview Questions and Answers
What is the main difference between CHAR and VARCHAR? CHAR has a fixed declared length and is commonly padded, while VARCHAR stores variable-length content up to a maximum.
When should TEXT or CLOB be used? For genuinely large documents or free-form content, after reviewing product-specific indexing and storage behavior.
What does collation control? It controls textual comparison and sorting, including possible case, accent, and locale rules.
Why are character count and byte count different? Variable-width encodings such as UTF-8 can use multiple bytes for one character.
Should phone numbers be numeric? Usually no; they are identifiers that can contain plus signs, leading zeros, and formatting.
Why can LOWER(column) hurt an index? A normal index on the original column may not support a transformed predicate unless the database has a matching functional design.
Is an empty string the same as NULL? Generally no, although Oracle traditionally treats empty character strings as NULL.
Key Takeaway
Character type design is a combination of textual meaning, length, Unicode support, comparison rules, search behavior, and constraints. CHAR is suitable for truly fixed-width domains, VARCHAR serves most bounded variable text, and large-text types handle content that is genuinely large.
CHARACTER TYPE DESIGN = TEXTUAL SEMANTICS
+ FIXED OR VARIABLE LENGTH
+ ENCODING AND COLLATION
+ CONSTRAINTS AND SEARCH
+ END-TO-END PRESERVATION
Prefer native types for nontextual values, choose international behavior deliberately, parameterize SQL, and test the complete data path. A good character column preserves what users mean while giving the database enough structure to validate, compare, index, and protect it correctly.