SQL String Functions

SQL provides a set of built-in string functions for working with text values. These functions can change the case of text, extract part of a string, join strings together, remove unwanted spaces, and more.

Think of string functions like text-editing tools inside a database query — similar to find-and-replace, trimming, or formatting text in a word processor, but applied directly to database column values.

Important: String functions do not change the actual data stored in the table. They only change how the data appears in the query result.

The Reference Table

String Function Reference

UPPER and LOWER

MySQL Query

SELECT FirstName, UPPER(FirstName) AS UpperName, LOWER(FirstName) AS LowerName
FROM Students;

-- Case-insensitive city comparison
SELECT * FROM Students WHERE LOWER(City) = 'delhi';

MS SQL Server Query

SELECT FirstName, UPPER(FirstName) AS UpperName, LOWER(FirstName) AS LowerName
FROM Students;

-- Case-insensitive city comparison
SELECT * FROM Students WHERE LOWER(City) = 'delhi';

String Length

MySQL Query

-- CHAR_LENGTH counts characters
SELECT FirstName, CHAR_LENGTH(FirstName) AS NameLength
FROM Students;

MS SQL Server Query

-- LEN() replaces CHAR_LENGTH() in MS SQL Server
SELECT FirstName, LEN(FirstName) AS NameLength
FROM Students;

CONCAT — Join Strings Together

MySQL Query

SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Students;

MS SQL Server Query

SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Students;

SUBSTRING, LEFT, RIGHT

MySQL Query

-- Extract first 3 characters of FirstName
SELECT FirstName, SUBSTRING(FirstName, 1, 3) AS ShortName FROM Students;

-- First 2 and last 2 characters
SELECT FirstName, LEFT(FirstName, 2) AS First2, RIGHT(FirstName, 2) AS Last2
FROM Students;

MS SQL Server Query

-- Extract first 3 characters of FirstName
SELECT FirstName, SUBSTRING(FirstName, 1, 3) AS ShortName FROM Students;

-- First 2 and last 2 characters
SELECT FirstName, LEFT(FirstName, 2) AS First2, RIGHT(FirstName, 2) AS Last2
FROM Students;

TRIM, LTRIM, RTRIM

MySQL Query

-- Remove all surrounding spaces
SELECT TRIM('   hello world   ') AS CleanText;

-- Remove only left or right spaces
SELECT LTRIM('   hello') AS LeftTrimmed;
SELECT RTRIM('hello   ') AS RightTrimmed;

MS SQL Server Query

-- TRIM() is supported from SQL Server 2017+
SELECT TRIM('   hello world   ') AS CleanText;

-- For older versions, use LTRIM and RTRIM together
SELECT LTRIM(RTRIM('   hello world   ')) AS CleanText;

SELECT LTRIM('   hello') AS LeftTrimmed;
SELECT RTRIM('hello   ') AS RightTrimmed;

REPLACE

MySQL Query

-- Replace 'gmail.com' with 'outlook.com' in the email column
SELECT Email, REPLACE(Email, 'gmail.com', 'outlook.com') AS NewEmail
FROM Students;

MS SQL Server Query

-- REPLACE works identically in MS SQL Server
SELECT Email, REPLACE(Email, 'gmail.com', 'outlook.com') AS NewEmail
FROM Students;

Find Position of a Substring

MySQL Query

-- INSTR(string, substring) -- returns position starting from 1
SELECT Email, INSTR(Email, '@') AS AtPosition FROM Students;

MS SQL Server Query

-- CHARINDEX(substring, string) -- note: argument order is REVERSED vs INSTR
SELECT Email, CHARINDEX('@', Email) AS AtPosition FROM Students;

Pad with Leading Zeros

MySQL Query

-- Pad StudentID with leading zeros to always show 4 digits
SELECT LPAD(StudentID, 4, '0') AS FormattedID, FirstName
FROM Students;

MS SQL Server Query

-- MS SQL Server has no LPAD(). Replicate it using REPLICATE() and RIGHT().
SELECT RIGHT(REPLICATE('0', 4) + CAST(StudentID AS NVARCHAR), 4) AS FormattedID,
       FirstName
FROM Students;

Extract Email Domain

MySQL Query

-- Extract everything after '@' in the email address
SELECT Email, SUBSTRING(Email, INSTR(Email, '@') + 1) AS Domain
FROM Students;

MS SQL Server Query

-- CHARINDEX replaces INSTR; SUBSTRING needs an explicit length
SELECT Email,
       SUBSTRING(Email, CHARINDEX('@', Email) + 1, LEN(Email)) AS Domain
FROM Students;

REVERSE

MySQL Query

SELECT FirstName, REVERSE(FirstName) AS Reversed FROM Students;

MS SQL Server Query

SELECT FirstName, REVERSE(FirstName) AS Reversed FROM Students;

Key Points

• String functions do not change data in the table — they only affect the query output.
• String positions in SQL start at 1, not 0.
• MySQL uses CHAR_LENGTH(); MS SQL Server uses LEN().
• MySQL uses INSTR(string, sub); MS SQL Server uses CHARINDEX(sub, string) — the argument order is reversed.
• MySQL has LPAD(); MS SQL Server does not — use RIGHT(REPLICATE(...) + str, n) instead.
• Functions identical in both: UPPER, LOWER, CONCAT, SUBSTRING, LEFT, RIGHT, LTRIM, RTRIM, REPLACE, REVERSE.