String functions are used to manipulate and work with text data in SQL. They help in formatting, extracting, and combining text for queries and reports.
Common String Functions
|
Function
|
Description
|
Example
|
|
CONCAT(str1, str2, …)
|
Combines multiple strings
|
CONCAT(first_name,' ', last_name)
|
|
SUBSTRING(str, start, length)
|
Extracts part of a string
|
SUBSTRING(name, 1, 3)
|
|
LENGTH(str)
|
Returns length of string
|
LENGTH(name)
|
|
REPLACE(str, old, new)
|
Replaces part of string
|
REPLACE(name, 'a', 'A')
|
|
UPPER(str)
|
Converts to uppercase
|
UPPER(name)
|
|
LOWER(str)
|
Converts to lowercase
|
LOWER(name)
|
|
TRIM(str)
|
Removes leading/trailing spaces
|
TRIM(name)
|
Example Table
CREATE TABLE users (
id INT,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
INSERT INTO users VALUES
(1, 'Ashika', 'Ashi'),
(2, 'Rohan', 'Kumar');
Example Queries
1. Combine first and last name:
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users;
Result:
full_name
----------
Ashika Ashi
Rohan Kumar
2. Extract first 3 letters of first name:
SELECT SUBSTRING(first_name, 1, 3) AS short_name
FROM users;
Result:
short_name
-----------
Ash
Roh
3. Replace letter in name:
SELECT REPLACE(first_name, 'a', 'A') AS new_name
FROM users;
Result:
new_name
---------
AshikA
RohAn
Key Points
- String functions can be nested:
- UPPER(CONCAT(first_name, ' ', last_name))
- Useful in reporting, formatting, and data cleaning
- Works with VARCHAR, CHAR, and TEXT data types
