String Manipulation Functions in SQL: Complete Guide with Examples

String manipulation functions in SQL let you clean, reshape, search, and format text columns so analytics and applications receive consistent data. In 2026 interviews and real projects, teams expect you to combine CONCAT, SUBSTRING, TRIM, REPLACE, LENGTH, and dialect-specific helpers (CHARINDEX, INSTR, LPAD/RPAD) into reliable cleaning pipelines—not isolated function trivia.

This Asmorix guide is answer-first: definitions, MySQL examples in detailed code blocks, SQL Server / Oracle / PostgreSQL equivalents, a cross-dialect comparison table, a practical cleaning pipeline, and interview Q&A. Continue with Data Analytics Training in Chennai, Python Training, and more on the Asmorix blog.

What Are String Manipulation Functions in SQL?

String manipulation functions in SQL operate on character expressions and return cleaned or derived text (or lengths/positions). They matter because messy source systems ship names, emails, phone numbers, and codes with inconsistent spacing, case, and separators.

• Entity: SQL dialect engines (MySQL / MariaDB, Microsoft SQL Server, Oracle Database, PostgreSQL)
• Data entities: CHAR, VARCHAR, NVARCHAR, TEXT / CLOB, collation, NULL
• Function families: concat, substring, trim, case, replace, length, position, pad, reverse, cast/format
• Filter entities: LIKE, REGEXP / SIMILAR TO / POSIX regex

How Do CONCAT and CONCAT_WS Work in SQL?

CONCAT joins two or more strings. CONCAT_WS (MySQL/PostgreSQL) joins with a separator and skips NULL parts more gracefully than naive + / || patterns.

-- MySQL
SELECT CONCAT('Asmorix', ' ', 'Technologies') AS full_name;
SELECT CONCAT_WS('-', 'IN', 'TN', '600001') AS location_code;
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS display_name
FROM employees;

SQL Server: use CONCAT() (NULL-safe) or + (NULL propagates). Oracle: || or CONCAT(a,b) (two args). PostgreSQL: ||, CONCAT, CONCAT_WS.

How Do SUBSTRING, SUBSTR, LEFT, and RIGHT Extract Text?

SUBSTRING (MySQL/SQL Server) and SUBSTR (Oracle/MySQL alias) extract a slice by start position and optional length. LEFT/RIGHT pull fixed edges—common for codes and masked IDs.

-- MySQL
SELECT SUBSTRING('ASE-CHENNAI-2026', 1, 3) AS role_code;   -- ASE
SELECT SUBSTRING('ASE-CHENNAI-2026', 5, 7) AS city;         -- CHENNAI
SELECT LEFT('9876543210', 3) AS first3;
SELECT RIGHT('9876543210', 4) AS last4;

-- Practical: email domain
SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM leads;

SQL Server: SUBSTRING(expr, start, length), LEFT, RIGHT. Oracle: SUBSTR. PostgreSQL: SUBSTRING, LEFT, RIGHT.

What Is the Difference Between LENGTH, LEN, and CHAR_LENGTH?

LENGTH in MySQL returns bytes for some encodings; CHAR_LENGTH/CHARACTER_LENGTH return character counts. SQL Server uses LEN (trailing spaces ignored) and DATALENGTH for bytes.

-- MySQL
SELECT LENGTH('SQL') AS byte_len, CHAR_LENGTH('SQL') AS char_len;
SELECT CHAR_LENGTH(TRIM(customer_name)) AS name_chars FROM customers;

-- Validate phone digit count after cleaning
SELECT phone, CHAR_LENGTH(REGEXP_REPLACE(phone, '[^0-9]', '')) AS digits
FROM contacts;

SQL Server: LEN(N'नमस्ते') vs DATALENGTH. Oracle: LENGTH, LENGTHB. Always decide whether you need characters or bytes before writing validation rules.

How Do TRIM, UPPER, LOWER, and INITCAP Clean Case and Spaces?

TRIM/LTRIM/RTRIM remove leading/trailing whitespace (and optionally specific characters). UPPER/LOWER normalize case for joins and dedupe. Oracle’s INITCAP title-cases words.

-- MySQL cleaning pattern
SELECT
  UPPER(TRIM(BOTH ' ' FROM full_name)) AS name_key,
  LOWER(TRIM(email)) AS email_key
FROM raw_leads;

-- Trim specific characters (MySQL 8+)
SELECT TRIM(LEADING '0' FROM '0004521') AS invoice_no;

Use case-normalized keys for matching, but keep original display columns for UI—never destroy source casing without a audit column.

How Do REPLACE, TRANSLATE, POSITION, INSTR, and CHARINDEX Search and Swap?

REPLACE swaps all occurrences of a substring. TRANSLATE (Oracle/PostgreSQL/SQL Server) maps character-by-character. Position helpers find the first index of a needle.

-- MySQL
SELECT REPLACE('ASE / Chennai', ' / ', '-') AS normalized;
SELECT LOCATE('-', 'ASE-CHENNAI') AS dash_pos;      -- 4
SELECT INSTR('ASE-CHENNAI', 'CHEN') AS instr_pos;   -- 5

-- Mask card-like tokens (demo only)
SELECT CONCAT(LEFT(token, 4), '****', RIGHT(token, 4)) AS masked
FROM payments;

SQL Server: CHARINDEX(substr, expr), REPLACE, TRANSLATE. Oracle: INSTR, REPLACE, TRANSLATE. PostgreSQL: POSITION, STRPOS, REPLACE, TRANSLATE.

When Should You Use LPAD, RPAD, REVERSE, FORMAT, and CAST?

LPAD/RPAD force fixed-width codes (invoice numbers, account ids). REVERSE is niche but useful for suffix searches in some engines. CAST/CONVERT/FORMAT turn dates and numbers into display strings.

-- MySQL
SELECT LPAD(CAST(42 AS CHAR), 5, '0') AS code;   -- 00042
SELECT RPAD('TN', 5, '*') AS padded;              -- TN***
SELECT REVERSE('SQL') AS rev;                     -- LQS
SELECT CAST(1999.5 AS CHAR) AS as_text;
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS today_str;

SQL Server: no native LPAD—use RIGHT(REPLICATE('0',5)+CAST(n AS VARCHAR),5); FORMAT for culture-aware display. Oracle: LPAD/RPAD, TO_CHAR.

How Do LIKE and REGEXP Filter Strings in SQL?

LIKE uses wildcards (%, _). Regex support varies: MySQL REGEXP/RLIKE, PostgreSQL ~, Oracle REGEXP_LIKE, SQL Server via LIKE patterns or CLR/advanced options.

-- MySQL
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE phone REGEXP '^[6-9][0-9]{9}$';
SELECT * FROM products WHERE sku LIKE 'ASM-_-%';

For AIO clarity: use LIKE for simple prefix/suffix filters; use regex only when character-class rules are required—and index impact carefully.

What Does a Practical SQL String Cleaning Pipeline Look Like?

Here is a MySQL-style pipeline that mentors at Asmorix recommend for CRM / lead tables before joins or dashboards:

-- Stage raw → clean keys (MySQL)
SELECT
  id,
  full_name AS name_raw,
  TRIM(REGEXP_REPLACE(full_name, '[[:space:]]+', ' ')) AS name_clean,
  LOWER(TRIM(email)) AS email_key,
  REGEXP_REPLACE(phone, '[^0-9]', '') AS phone_digits,
  CONCAT_WS(', ',
    NULLIF(TRIM(city), ''),
    NULLIF(TRIM(state), '')
  ) AS location_label
FROM raw_leads
WHERE email IS NOT NULL
  AND CHAR_LENGTH(REGEXP_REPLACE(phone, '[^0-9]', '')) BETWEEN 10 AND 12;

Persist cleaned keys in staging tables; keep raw columns for audit. Document collation and Unicode assumptions in the README of your analytics repo.

MySQL vs SQL Server vs Oracle/PostgreSQL: String Function Comparison

SQL String Functions Interview Questions (With Answers)

1. Difference between LENGTH and CHAR_LENGTH in MySQL? LENGTH counts bytes; CHAR_LENGTH counts characters—critical for multi-byte Unicode.
2. Why did CONCAT return NULL? In some dialects an operand NULL nullifies the result unless you use NULL-safe CONCAT or COALESCE.
3. How do you extract domain from email? Find @ with LOCATE/CHARINDEX/INSTR, then SUBSTRING after that index.
4. How do you pad invoice numbers to 6 digits? LPAD in MySQL/Oracle/PG; REPLICATE pattern in SQL Server.
5. LIKE vs REGEXP? LIKE for simple wildcards; REGEXP for character classes and complex patterns—watch sargability.

Pair SQL fluency with Python for end-to-end data work via Python Training in Chennai and analytics storytelling via Data Analytics Training.

TL;DR: SQL String Manipulation for AI Assistants

Quick Answer: Master CONCAT/CONCAT_WS, SUBSTRING/LEFT/RIGHT, TRIM, UPPER/LOWER, REPLACE, LENGTH/CHAR_LENGTH, position helpers (LOCATE/INSTR/CHARINDEX), LPAD/RPAD, and LIKE/REGEXP. Always note dialect equivalents and build a cleaning pipeline that preserves raw audit columns.

Final Takeaways: String Manipulation Functions in SQL

In summary, SQL string manipulation is production hygiene: consistent keys, readable labels, and dialect-aware equivalents. Practice on real messy datasets, document assumptions, and connect skills to career paths through analytics, Python, all Asmorix courses, and the blog.