SQL STRING FUNCTIONS — সম্পূর্ণ ২ ঘন্টার লাইভ ক্লাস নোট

01

কেন String Functions?

১৫ মিনিট

১০ লাখ customer — নামগুলো নোংরা:

" Rahim Khan " "karim hossain" "JANNAT AHMED" " Md. Hasan "

জিজ্ঞেস

১ million customer manually ঠিক করা সম্ভব? Manager চায়: format, trim, upper/lower email, first name, phone country code।

Raw Text → String Functions → Clean Text → Useful Info → Business Report

Sequence: এই module = string map/overview · তারপর Module 50 CONCAT → 51 LENGTH → 52 LOCATE (deep)।

02

String কী?

৮ মিনিট

String = text / character-এর collection। উদা: "Rahim", "Dhaka", "rahim@gmail.com"।

Data	Type	Meaning
100	Number	সংখ্যা
"100"	String	Text
"Rahim"	String	নাম
"Dhaka"	String	শহর

সংখ্যায় গণিত · string-এ text কাজ — তাই type গুরুত্বপূর্ণ।

03

Database-এ String

৮ মিনিট

customer_id	name	email	city	phone
1	Rahim Khan	RAHIM@GMAIL.COM	dhaka	01712345678
2	Karim Hossain	karim@gmail.com	DHAKA	01812345678
3	Jannat Ahmed	JANNAT@YAHOO.COM	Dhaka	01912345678
4	Md Hasan	mdhasan@gmail.com	Chittagong	01612345678

Problems: mixed case, city inconsistency, messy formats।

04

Function Map

৬ মিনিট

STRING OPERATIONS CHANGE: LOWER UPPER CLEAN: TRIM LTRIM RTRIM EXTRACT: SUBSTRING LEFT RIGHT COMBINE: CONCAT CONCAT_WS SEARCH/MODIFY: LOCATE INSTR REPLACE (+ LIKE = operator, not a function)

05

LOWER()

৮ মিনিট

SELECT LOWER(email) AS clean_email FROM customers;

rahim@gmail.com karim@gmail.com jannat@yahoo.com

06

UPPER()

৬ মিনিট

SELECT UPPER(name) AS customer_name FROM customers;

rahim → UPPER() → RAHIM

Reports, codes, category labels-এ ব্যবহার।

07

LOWER vs UPPER

৫ মিনিট

Function	কাজ
LOWER()	সব ছোট হাতের
UPPER()	সব বড় হাতের

"DaTa ScIeNcE"

LOWER → data science · UPPER → DATA SCIENCE

08

TRIM()

৮ মিনিট

" Rahim Khan " → TRIM() → "Rahim Khan"

SELECT TRIM(name) AS clean_name FROM customers;

Data cleaning-এর প্রথম ধাপ প্রায়ই TRIM।

09

LTRIM() / RTRIM()

৬ মিনিট

LTRIM(" Rahim") → "Rahim" RTRIM("Rahim ") → "Rahim"

Function	Removes
TRIM()	Left + Right
LTRIM()	Left only
RTRIM()	Right only

10

CONCAT() — map only

২ মিনিট

একাধিক value জোড়া → CONCAT(first_name, ' ', last_name) → Rahim Khan।

Deep dive = Module 50 · sql_concat_bangla.html — এখানে পুনরায় শেখানো হচ্ছে না।

11

CONCAT_WS() — map only

২ মিনিট

Separator একবার: CONCAT_WS(', ', city, country) → Dhaka, Bangladesh।

বিস্তারিত + NULL behavior → Module 50 CONCAT।

12

LENGTH() — map only

২ মিনিট

MySQL-এ LENGTH() ≈ bytes (ASCII-এ "Rahim" → 5)।

Deep dive = Module 51 · sql_length_bangla.html।

13

LENGTH vs CHAR_LENGTH — pointer

২ মিনিট

LENGTH = bytes · CHAR_LENGTH = characters (বাংলা/multibyte detail → Module 51)

পার্থক্য ও lab → Module 51 LENGTH।

14

SUBSTRING()

১০ মিনিট

BD-DHK-2026-00125 SUBSTRING(code, 4, 3) → DHK SUBSTRING(text, start, length)

SELECT SUBSTRING(customer_code, 4, 3) AS city_code FROM customers;

More

SUBSTRING('ABCDEF',1,3)=ABC SUBSTRING('ABCDEF',2,2)=BC SUBSTRING('ABCDEF',4,3)=DEF SUBSTRING('phone:017',7,3)=017 SUBSTRING('SKU-IT-99',5,2)=IT

15

LEFT()

৫ মিনিট

SELECT LEFT(customer_code, 2) FROM customers;

BD

BD-DHK-2026 ↑↑ LEFT()

16

RIGHT()

৫ মিনিট

SELECT RIGHT(customer_code, 4) FROM customers;

2026

BD-DHK-2026 ↑↑↑↑ RIGHT()

17

LEFT vs RIGHT vs SUBSTRING

৬ মিনিট

Function	Extracts	BD-DHK-2026
LEFT()	Beginning	LEFT(…,2)=BD
RIGHT()	Ending	RIGHT(…,4)=2026
SUBSTRING()	Position	SUBSTRING(…,4,3)=DHK

18

REPLACE()

১০ মিনিট

SELECT REPLACE(city, ' City', '') AS city_name FROM customers; SELECT REPLACE(phone, '-', '') FROM customers; SELECT REPLACE(email, 'gmail.com', 'company.com') FROM customers;

"Dhaka City" → REPLACE → "Dhaka"

SELECT-এ REPLACE শুধু result বদলায় — UPDATE না করলে table অপরিবর্তিত।

19

REVERSE()

৪ মিনিট

SELECT REVERSE(name) FROM customers;

Rahim → mihaR

বেশি demo/helper — daily business report-এ কম।

20

LOCATE() — map only

২ মিনিট

Substring-এর position: LOCATE('@', email) → 1-based index (না থাকলে 0)।

Deep dive = Module 52 · sql_locate_bangla.html।

21

INSTR()

৩ মিনিট

INSTR(email, '@') — LOCATE-এর মতো position খোঁজা।

LOCATE deep dive + start-position → Module 52।

22

Extract Email Domain

৬ মিনিট

LOCATE এখানে শুধু composition — গভীর LOCATE = Module 52।

SELECT email, SUBSTRING(email, LOCATE('@', email) + 1) AS domain FROM customers;

23

Extract First Name

৬ মিনিট

Composition example — LOCATE masterclass = Module 52।

SELECT name, LEFT(name, LOCATE(' ', name) - 1) AS first_name FROM customers;

24

LIKE (operator)

১০ মিনিট

LIKE = string function নয় — pattern-matching condition/operator। গভীর শেখার জন্য LIKE module দেখো।

SELECT * FROM customers WHERE name LIKE 'Rahim%'; SELECT * FROM customers WHERE name LIKE '%Khan%'; SELECT * FROM customers WHERE name LIKE 'R_him';

% = যেকোনো সংখ্যক character _ = এক character

25

Cleaning Pipeline

৮ মিনিট

" RAHIM KHAN " → TRIM → remove outer spaces → LOWER/UPPER as needed → REPLACE if required → CLEAN TEXT "Rahim Khan" (with further title-case logic outside SQL or CASE)

26

Nested Functions

৮ মিনিট

SELECT UPPER(TRIM(name)) AS cleaned_name FROM customers; SELECT LOWER(TRIM(email)) AS cleaned_email FROM customers;

name → TRIM() → UPPER() → Final (ভিতর থেকে বাইরে পড়ো)

27

CASE + LIKE

৮ মিনিট

SELECT CASE WHEN email LIKE '%gmail.com' THEN 'Gmail' WHEN email LIKE '%yahoo.com' THEN 'Yahoo' ELSE 'Other' END AS email_provider FROM customers;

28

Case Study — Customer Cleaning

১২ মিনিট

Dirty: " Rahim Khan " / "RAHIM KHAN" / "rahim khan"

TRIM spaces
Standardize case
Full names
First names
Email domains
Gmail users
Name length
Replace junk
Phone standardize
Clean report

SELECT TRIM(name) AS name_clean, LOWER(TRIM(email)) AS email_clean, REPLACE(phone, '-', '') AS phone_clean FROM customers;

29

Case Study — Products

৮ মিনিট

SELECT TRIM(product_name), UPPER(category), LEFT(sku, 3), CONCAT(brand, ' - ', TRIM(product_name)) FROM products WHERE product_name LIKE '%Phone%';

30

Case Study — HR

৮ মিনিট

SELECT CONCAT(first_name, ' ', last_name) AS full_name, LOWER(CONCAT(first_name, '.', last_name, '@company.com')) AS corp_email, LEFT(employee_code, 2) AS code_prefix, CHAR_LENGTH(first_name) AS fname_len FROM employees WHERE last_name LIKE 'Khan%';

31

NULL vs '' vs space

৮ মিনিট

NULL = unknown / missing '' = empty string ' ' = space character

SELECT COALESCE(phone, 'No Phone') AS phone FROM customers;

32

CONCAT + NULL — pointer

২ মিনিট

CONCAT-এ NULL থাকলে result NULL হতে পারে — COALESCE দিয়ে বাঁচানো যায়।

বিস্তারিত lab = Module 50 CONCAT।

33

Full Comparison Table

৬ মিনিট

Function	Purpose	Example
LOWER/UPPER	case	rahim / RAHIM
TRIM/LTRIM/RTRIM	spaces	Rahim
LENGTH/CHAR_LENGTH	bytes/chars	5
CONCAT/CONCAT_WS	join	Rahim Khan
LEFT/RIGHT/SUBSTRING	extract	BD / 2026 / DHK
REPLACE/REVERSE	modify	Dhaka / mihaR
LOCATE/INSTR	position	6

34

Must Know vs Good to Know

৫ মিনিট

Must Know

LOWER UPPER TRIM CONCAT LENGTH CHAR_LENGTH SUBSTRING LEFT RIGHT REPLACE LIKE

Good to Know

LTRIM RTRIM CONCAT_WS LOCATE INSTR REVERSE COALESCE

35

Predict the Output (১৫+)

১২ মিনিট

Predict: UPPER('data science')

DATA SCIENCE

Predict: LOWER('DaTa')

data

Predict: TRIM(' A ')

A

Predict: LENGTH('Rahim')

5

Predict: CONCAT('A','B')

AB

Predict: LEFT('BD-DHK',2)

BD

Predict: RIGHT('BD-DHK-2026',4)

2026

Predict: SUBSTRING('ABCDEF',2,3)

BCD

Predict: REPLACE('Dhaka City',' City','')

Dhaka

Predict: REVERSE('ABC')

CBA

Predict: LOCATE('@','a@b.com')

2

Predict: 'Rahim' LIKE 'R%'

TRUE / match

Predict: 'Rahim' LIKE 'R_him'

TRUE

Predict: UPPER(TRIM(' x '))

X

Predict: CONCAT_WS('-','A','B')

A-B

36

Which Function? (২০)

১০ মিনিট

Text ছোট হাতের?

LOWER()

বড় হাতের?

UPPER()

দুই column এক?

CONCAT() / CONCAT_WS()

শুরুর 3 character?

LEFT(...,3)

শেষের 4?

RIGHT(...,4)

মাঝের অংশ?

SUBSTRING()

বাইরের space?

TRIM()

বাম space?

LTRIM()

ডান space?

RTRIM()

কত character?

CHAR_LENGTH()

কত byte?

LENGTH()

text replace?

REPLACE()

@ কোথায়?

LOCATE()/INSTR()

pattern search?

LIKE

email domain?

SUBSTRING + LOCATE('@')

first name?

LEFT + LOCATE(' ')

NULL fallback?

COALESCE()

separator join?

CONCAT_WS()

reverse demo?

REVERSE()

gmail users?

WHERE email LIKE '%gmail.com'

37

Common Mistakes (১৫)

১০ মিনিট

String vs number গুলিয়ে
Text-এ quotes ভুলে
Wrong quotation marks
CONCAT-এ comma ভুলে
LENGTH vs CHAR_LENGTH
LEFT vs SUBSTRING
Pattern-এ = ব্যবহার
LIKE-এ % ভুলে
% ভুল জায়গায়
CONCAT-এ space ভুলে
NULL ignore
'' ও NULL এক ভাবা
আগে SELECT না করে UPDATE
REPLACE সব match না বোঝা
Nested inside-out না পড়া

Pattern

WRONG → WHY? → CORRECT

38

String in Data Cleaning

৬ মিনিট

RAW → CLEANING → STANDARDIZATION → TRANSFORMATION→ ANALYSIS → REPORT / DASHBOARD Uses: email/phone/product/address/category normalize, ETL, dashboards

39

Mini Project — Customer Cleaning

১৫ মিনিট

২০+ dirty rows: extra spaces, mixed case, phones, missing values।

Clean name
Clean email
First name
Email provider
Gmail customers
Name contains Rahim
Name length
City + Country
Standard phone
Final clean report

40

Professional Workflows

৬ মিনিট

Analyst: CSV → SQL clean → Analyze → Dashboard Engineer: Source → ETL → String transform → Warehouse Scientist: Raw text → Clean → Features → ML BI: DB → SQL transform → Clean dims → Dashboard

41

Performance & Best Practices

৬ মিনিট

WHERE-এ function অপ্রয়োজনে index বাধা দিতে পারে
Clean data আগে store করা ভালো হতে পারে
Huge table-এ বারবার heavy transform এড়াও
Aliases ব্যবহার করো
UPDATE আগে SELECT দিয়ে test

42

SELECT → then UPDATE

৮ মিনিট

SELECT → Check → Confirm → UPDATE → Verify

SELECT LOWER(TRIM(email)) FROM customers; UPDATE customers SET email = LOWER(TRIM(email));

43

Interview Top 30

১৫ মিনিট

IV 1. String কী?

text collection · উদা: Rahim · ফলো: vs number · ইউজ: names

IV 2. String functions?

clean/change/extract tools · ভুল: only LIKE · ফলো: list · ইউজ: ETL

IV 3. LOWER vs UPPER?

case down/up · ভুল: trim · ফলো: email · ইউজ: standardize

IV 4. TRIM?

outer spaces · ভুল: middle all · ফলো: L/R · ইউজ: clean

IV 5. LTRIM vs RTRIM?

left vs right · ভুল: both · ফলো: TRIM · ইউজ: import

IV 6. CONCAT?

join pieces · ভুল: + only · ফলো: space · ইউজ: full name

IV 7. CONCAT_WS?

join with separator · ভুল: same as CONCAT · ফলো: city,country · ইউজ: address

IV 8. LENGTH?

bytes MySQL · ভুল: always chars · ফলো: Bangla · ইউজ: DQ

IV 9. CHAR_LENGTH?

characters · ভুল: bytes · ফলো: utf8 · ইউজ: UI limits

IV 10. LENGTH vs CHAR_LENGTH?

bytes vs chars · ভুল: identical · ফলো: demo · ইউজ: i18n

IV 11. SUBSTRING?

start+len extract · ভুল: only left · ফলো: code · ইউজ: SKU

IV 12. LEFT?

from start · ভুল: end · ফলো: BD · ইউজ: prefix

IV 13. RIGHT?

from end · ভুল: start · ফলো: year · ইউজ: suffix

IV 14. LEFT vs SUBSTRING?

fixed start vs position · ভুল: same always · ফলো: examples · ইউজ: choose

IV 15. REPLACE?

swap text · ভুল: one char only · ফলো: phone · ইউজ: normalize

IV 16. LOCATE?

find position · ভুল: extract · ফলো: @ · ইউজ: parse

IV 17. INSTR?

similar position · ভুল: different always · ফলো: MySQL · ইউজ: search

IV 18. LIKE?

pattern op · ভুল: function · ফলো: % _ · ইউজ: filter

IV 19. % ?

any length · ভুল: one char · ফলো: examples · ইউজ: contains

IV 20. _ ?

one char · ভুল: many · ফলো: R_him · ইউজ: templates

IV 21. Email domain?

SUBSTRING after @ · ভুল: REPLACE only · ফলো: LOCATE · ইউজ: provider

IV 22. First name?

LEFT before space · ভুল: RIGHT · ফলো: LOCATE · ইউজ: CRM

IV 23. Remove spaces?

TRIM · ভুল: REPLACE space once only · ফলো: nest · ইউজ: clean

IV 24. Standardize email?

LOWER(TRIM(email)) · ভুল: UPPER · ফলো: UPDATE later · ইউজ: login

IV 25. NULL text?

COALESCE · ভুল: ignore · ফলো: reports · ইউজ: display

IV 26. NULL vs ''?

missing vs empty · ভুল: same · ফলো: COUNT · ইউজ: DQ

IV 27. Full name?

CONCAT first last · ভুল: no space · ফলো: COALESCE · ইউজ: HR

IV 28. Search text?

LIKE / LOCATE · ভুল: = only · ফলো: % · ইউজ: find

IV 29. Clean customers?

TRIM LOWER REPLACE pipeline · ভুল: manual · ফলো: SELECT first · ইউজ: warehouse

IV 30. ETL use?

transform text dims · ভুল: only BI UI · ফলো: CASE · ইউজ: pipelines

44

MCQ (২৫+)

১২ মিনিট

MCQ 1. String is? A) only numbers B) text C) JOIN D) index

B

MCQ 2. LOWER does? A) trim B) lowercase C) reverse D) sum

B

MCQ 3. UPPER does? A) uppercase B) length C) locate D) like

A

MCQ 4. TRIM removes? A) middle always B) outer spaces C) digits D) NULL

B

MCQ 5. CONCAT joins? A) tables B) text pieces C) indexes D) rows only

B

MCQ 6. CONCAT_WS needs? A) separator first B) no sep C) GROUP BY D) JOIN

A

MCQ 7. MySQL LENGTH? A) bytes B) always chars C) rows D) tables

A

MCQ 8. CHAR_LENGTH? A) bytes B) characters C) KB D) columns

B

MCQ 9. LEFT('ABCD',2)? A) CD B) AB C) BC D) AD

B

MCQ 10. RIGHT('ABCD',2)? A) AB B) CD C) BC D) AA

B

MCQ 11. SUBSTRING('ABCD',2,2)? A) AB B) BC C) CD D) AD

B

MCQ 12. REPLACE role? A) swap text B) sort C) join tables D) count

A

MCQ 13. LOCATE returns? A) text B) position C) table D) NULL always

B

MCQ 14. LIKE is? A) function always B) pattern operator C) aggregate D) PK

B

MCQ 15. % means? A) one char B) any length C) digit D) space

B

MCQ 16. _ means? A) any length B) one char C) zero D) digit only

B

MCQ 17. COALESCE use? A) NULL fallback B) trim C) upper D) reverse

A

MCQ 18. Nested UPPER(TRIM(x)) order?

TRIM first then UPPER

MCQ 19. SELECT REPLACE updates table? A) yes B) no C) always D) drops

B

MCQ 20. "100" type? A) number B) string C) bool D) date

B

MCQ 21. LTRIM removes? A) left spaces B) right C) both D) digits

A

MCQ 22. Email domain tool combo?

LOCATE + SUBSTRING

MCQ 23. First name tool combo?

LOCATE space + LEFT

MCQ 24. Safe before UPDATE?

SELECT check first

MCQ 25. INSTR similar to? A) SUM B) LOCATE C) AVG D) JOIN

B

MCQ 26. Must-know set includes?

LOWER TRIM CONCAT SUBSTRING LIKE

45

Viva

১০ মিনিট

Viva 1. String কী?

text

Viva 2. LOWER?

ছোট হাতের

Viva 3. UPPER?

বড় হাতের

Viva 4. TRIM?

দুই পাশের space

Viva 5. CONCAT?

join text

Viva 6. CONCAT_WS?

separator দিয়ে join

Viva 7. LENGTH?

bytes

Viva 8. CHAR_LENGTH?

characters

Viva 9. LEFT?

শুরু থেকে

Viva 10. RIGHT?

শেষ থেকে

Viva 11. SUBSTRING?

position থেকে

Viva 12. REPLACE?

text বদল

Viva 13. LOCATE?

position খোঁজা

Viva 14. LIKE?

pattern operator

Viva 15. % ?

যেকোনো দৈর্ঘ্য

Viva 16. _ ?

এক character

Viva 17. Domain extract?

after @

Viva 18. First name?

before space

Viva 19. NULL vs ''?

missing vs empty

Viva 20. COALESCE?

fallback

Viva 21. Nested order?

inside-out

Viva 22. SELECT vs UPDATE?

test then write

Viva 23. Cleaning path?

TRIM→case→REPLACE

Viva 24. ETL?

transform text

Viva 25. Must-know list?

LOWER UPPER TRIM CONCAT LENGTH SUBSTRING LEFT RIGHT REPLACE LIKE

Viva 26. Final definition?

tools to clean/change/search/extract/combine text

46

Classroom Exercises (২০)

১২ মিনিট

Ex 1 — UPPER name

SELECT UPPER(name) FROM customers

Ex 2 — LOWER email

SELECT LOWER(email) FROM customers

Ex 3 — TRIM name

SELECT TRIM(name) FROM customers

Ex 4 — CHAR_LENGTH

SELECT CHAR_LENGTH(name) FROM customers

Ex 5 — CONCAT first last

CONCAT(first_name,' ',last_name)

Ex 6 — LEFT 3

LEFT(code,3)

Ex 7 — RIGHT 4

RIGHT(code,4)

Ex 8 — SUBSTRING

SUBSTRING(code,4,3)

Ex 9 — REPLACE City

REPLACE(city,' City','')

Ex 10 — LIKE Khan

WHERE name LIKE '%Khan%'

Ex 11 — Email domain

SUBSTRING(email, LOCATE('@',email)+1)

Ex 12 — First name

LEFT(name, LOCATE(' ',name)-1)

Ex 13 — Full location

CONCAT_WS(', ',city,country)

Ex 14 — Standard email

LOWER(TRIM(email))

Ex 15 — Standard phone

REPLACE(phone,'-','')

Ex 16 — Gmail users

WHERE LOWER(email) LIKE '%gmail.com'

Ex 17 — Dhaka customers

WHERE LOWER(city)='dhaka' OR city LIKE 'Dhaka%'

Ex 18 — Long names

WHERE CHAR_LENGTH(TRIM(name)) > 12

Ex 19 — Clean products

TRIM + UPPER(category)

Ex 20 — Cleaning report

SELECT cleaned cols pipeline

47

Final Cheat Sheet

৫ মিনিট

LOWER() → ছোট হাতের
UPPER() → বড় হাতের
TRIM()/LTRIM()/RTRIM() → spaces
CONCAT()/CONCAT_WS() → join
LENGTH()/CHAR_LENGTH() → bytes/chars
LEFT()/RIGHT()/SUBSTRING() → extract
REPLACE()/REVERSE() → modify
LOCATE()/INSTR() → position
LIKE → pattern search (operator)
COALESCE() → NULL fallback

48

Memory Map + Final Model

৫ মিনিট

SQL STRING CLEAN: TRIM LTRIM RTRIM CHANGE: LOWER UPPER JOIN: CONCAT CONCAT_WS EXTRACT: LEFT RIGHT SUBSTRING SEARCH: LIKE LOCATE INSTR MODIFY: REPLACE DATABASE → TEXT → dirty? → STRING FUNCTIONS → CLEAN → STANDARDIZE → EXTRACT → COMBINE → SEARCH → BUSINESS DATA

SQL String Functions হলো database-এর text data-কে clean, change, search, extract এবং combine করার tools।

বিষয়সূচী

কেন String Functions?

String কী?

Database-এ String

Function Map

LOWER()

UPPER()

LOWER vs UPPER

TRIM()

LTRIM() / RTRIM()

CONCAT() — map only

CONCAT_WS() — map only

LENGTH() — map only

LENGTH vs CHAR_LENGTH — pointer

SUBSTRING()

LEFT()

RIGHT()

LEFT vs RIGHT vs SUBSTRING

REPLACE()

REVERSE()

LOCATE() — map only

INSTR()

Extract Email Domain

Extract First Name

LIKE (operator)

Cleaning Pipeline

Nested Functions

CASE + LIKE

Case Study — Customer Cleaning

Case Study — Products

Case Study — HR

NULL vs '' vs space

CONCAT + NULL — pointer

Full Comparison Table

Must Know vs Good to Know

Must Know

Good to Know

Predict the Output (১৫+)

Which Function? (২০)

Common Mistakes (১৫)

String in Data Cleaning

Mini Project — Customer Cleaning

Professional Workflows

Performance & Best Practices

SELECT → then UPDATE

Interview Top 30

MCQ (২৫+)

Viva

Classroom Exercises (২০)

Final Cheat Sheet

Memory Map + Final Model