Content ITV PRO
This is Itvedant Content department
Unlock the Built-Ins
String Manipulation Magic
Learning Outcome
4
Clean and analyze textual data
3
Modify and format text data
2
Use common string functions like UPPER, LOWER, LENGTH
1
Understand what String Functions are in SQL.
Let’s recall:
Select retrieves data
WHERE filters records
ORDER BY sorts results
Data in tables can be numbers or text
Question:
What if Instagram wants to:
Convert usernames to uppercase on profile?
Count characters in bio? or Show length of bio for validation
Combine first name and last name?
Combine first name and last name?
Extract part of a hashtag?
Extract part of a hashtag?
Count characters in bio? or Show length of bio for validation
Convert usernames to uppercase on profile?
All these require text manipulation
SQL provides String Built-in Functions for this purpose.
From the story:
Text data needs formatting.
Text data needs formatting.
Sometimes text must be cleaned
Sometimes text must be cleaned
Sometimes text must be modified
Sometimes text must be modified
SQL provides built-in functions to work with text.
SQL provides built-in functions to work with text.
These are called String Function.
What are String Functions?
String functions are used to:
They work on:
Modify Text
Analyze Text
Format Text
Varchar
char
Text columns
UPPER() Function
Purpose
Converts text to Uppercase.
Instagram Example:
SELECT UPPER(username)
FROM users;
Converts dipali_ig → DIPALI_IG
Lower() Function
Purpose
Converts text to Lowercase.
SELECT LOWER(username)
FROM users;
Useful for case-insensitive comparisons.
LENGTH() Function
Purpose
Returns number of characters in string.
SELECT username, LENGTH(bio)
FROM users;
Helps Instagram check bio character limit.
Instagram Example:
CONCAT() Function
Purpose
Combines multiple strings.
SELECT CONCAT(first_name, ' ', last_name)
FROM users;
Combines first and last name into full name.
Instagram Example:
SUBSTRING() Function
Purpose
Extracts part of string.
SELECT SUBSTRING(username, 1, 4)
FROM users;
Extracts first 4 characters of username.
Instagram Example:
TRIM() Function
Purpose
Removes extra spaces.
SELECT TRIM(username)
FROM users;
Cleans accidental spaces in usernames.
A
M
A
N
REPLACE() Function
Purpose
Replaces part of string.
SELECT REPLACE(bio, 'insta', 'Instagram')
FROM users;
Corrects word formatting.
Instagram Example:
Summary
4
Essential for analytics and development
3
Helps improve data presentation
2
Common functions: UPPER, LOWER, LENGTH, CONCAT.
1
SQL provides many string functions
Quiz
Which function combines two strings?
A. MERGE()
B. ADD()
C. CONCAT()
D. JOIN()
Quiz-Answer
C. CONCAT()
D. JOIN()
A. MERGE()
B. ADD()
Which function combines two strings?
SQL - String Manipulation Magic
By Content ITV
