SQL String Functions (CONCAT, SUBSTRING, LENGTH, REPLACE, TRIM, ...)

🔹 Why Learn String Functions?

String manipulation powers data cleaning, formatting, and reporting. SQL provides built-in functions to concatenate text, extract substrings, remove whitespace, replace values, and adjust case.

⚙️ Common Functions at a Glance

Function	Purpose	Example
CONCAT()	Combine strings	`CONCAT(first_name, ' ', last_name)`
SUBSTRING()	Extract characters	`SUBSTRING('Database', 1, 4)` → `Data`
LENGTH()	Count characters	`LENGTH('SQL')` → `3`
TRIM()	Remove spaces	`TRIM(' SQL ')` → `SQL`
REPLACE()	Swap text	`REPLACE('Hello SQL', 'SQL', 'World')`
UPPER()/LOWER()	Change case	`UPPER('sql')` → `SQL`
LEFT()/RIGHT()	Grab start or end	`LEFT('Database', 4)` → `Data`
POSITION()	Find substring index	`POSITION('@' IN email)`
CONCAT_WS()	Concatenate with delimiter	`CONCAT_WS('-', '2025', '10', '24')`

🧠 Example 1 – Concatenate Names

SELECT
  CONCAT(first_name, ' ', last_name) AS full_name,
  department
FROM employees;

Produces readable names for reports and exports.

🧠 Example 2 – Extract a Substring

SELECT SUBSTRING('Information', 1, 4) AS extract_part;
-- Output: Info

Use SUBSTRING to pull a portion of a string starting at a specific position.

🧠 Example 3 – Trim Whitespace

SELECT
  TRIM('   SQL Functions   ')   AS trimmed,
  LTRIM('   SQL')              AS left_trimmed,
  RTRIM('SQL   ')              AS right_trimmed;

Critical when cleaning user-entered data.

🧠 Example 4 – Replace Text

SELECT REPLACE('I love SQL', 'SQL', 'Databases') AS new_text;

📘 Useful for quick data corrections or formatting adjustments.

🧠 Example 5 – Change Case

SELECT
  UPPER('developer')      AS upper_case,
  LOWER('SQL TRAINING')   AS lower_case;

🧠 Example 6 – Validate with Length and Position

SELECT
  email,
  LENGTH(email) AS email_length,
  POSITION('@' IN email) AS at_pos
FROM users;

Combine length and position checks to flag malformed emails.

🧠 Example 7 – LEFT() and RIGHT()

SELECT
  LEFT('Database', 4)  AS left_part,
  RIGHT('Database', 4) AS right_part;

⚙️ Combine Functions for Custom Parsing

SELECT
  LOWER(LEFT(email, POSITION('@' IN email) - 1)) AS username
FROM users;

💡 Stack functions together to build powerful parsing pipelines directly in SQL.

⚖️ Function Name Variations

Function	MySQL	SQL Server	PostgreSQL	Oracle
String length	LENGTH()	LEN()	LENGTH()	LENGTH()
Substring	SUBSTRING()	SUBSTRING()	SUBSTRING()	SUBSTR()
Position	POSITION()	CHARINDEX()	POSITION()	INSTR()
Concatenate	CONCAT()	+	CONCAT()	CONCAT()

💡 Interview Soundbite

“SQL string functions—CONCAT, SUBSTRING, TRIM, LENGTH, REPLACE—are essential for cleaning and formatting text fields before analytics or reporting.”

← Previous Next →

← Back to Articles | 🏠 Back to Homepage