SQL LOCATE() — সম্পূর্ণ ২ ঘন্টার লাইভ ক্লাস নোট

01

কেন LOCATE()?

১৫ মিনিট

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

জিজ্ঞেস

Email-এ @ কোথায়? চোখে দেখা যায় — database-কে কীভাবে বলব position?

rahim@gmail.com ↑ @ LOCATE() → Position

LOCATE() = এক text-এর ভিতরে অন্য text কোথায় আছে — তার starting position।

02

Real-life Stories

১৫ মিনিট

Email: rahim@gmail.com → @ কোথায়? Code: BD-DHK-2026-001 → DHK কোথা থেকে? Name: Mohammad Rahim Khan → Rahim কোথায়? URL: https://example.com → :// কোথায়? Order: ORDER-BD-DHK-1001 → BD কোথায়? Full Text → Search Text → LOCATE() → Position

03

Position বোঝা

১০ মিনিট

H E L L O 1 2 3 4 5 SQL-এ position সাধারণত 1 থেকে শুরু। দুইটি L → LOCATE first occurrence দেয় (position 3)

04

LOCATE() কী?

১০ মিনিট

LOCATE(search_string, text);

LOCATE() ├── search_string (কী খুঁজছি?) └── text (কোথায় খুঁজছি?)

SELECT LOCATE('L', 'HELLO');

3

05

Simple Examples

১৫ মিনিট

SELECT LOCATE('a', 'Bangladesh'); -- 2 SELECT LOCATE('SQL', 'I love SQL'); -- 8 SELECT LOCATE('Data', 'Data Science'); -- 1 SELECT LOCATE('Science', 'Data Science'); -- 6

B a n g l a d e s h 1 2 3 4 5 6 7 8 9 10 ↑ first 'a'

06

Character vs Word

১০ মিনিট

SELECT LOCATE('a', 'Bangladesh'); SELECT LOCATE('desh', 'Bangladesh'); SELECT LOCATE('ng', 'Bangladesh'); SELECT LOCATE('Ban', 'Bangladesh'); SELECT LOCATE('love', 'I love SQL'); SELECT LOCATE('Data Science', 'Learn Data Science now'); SELECT LOCATE('@', 'a@b.com'); SELECT LOCATE('://', 'https://x.com');

এক character বা পুরো word/substring — দুটোই LOCATE খুঁজতে পারে।

07

First Occurrence

১০ মিনিট

B A N A N A 1 2 3 4 5 6

SELECT LOCATE('A', 'BANANA'); -- 2 (not 4 or 6) SELECT LOCATE('N', 'BANANA'); -- 3

Default = প্রথম occurrence-এর position।

08

Table Columns

১৫ মিনিট

CREATE TABLE customers ( customer_id INT, customer_name VARCHAR(100), email VARCHAR(100) ); INSERT INTO customers VALUES (1,'Rahim Khan','rahim@gmail.com'), (2,'Karim Hossain','karim@yahoo.com'), (3,'Jannat Ahmed','jannat@gmail.com'), (4,'Sakib Hasan','sakib@outlook.com'), (5,'Nadia Rahman','nadia@gmail.com'); SELECT email, LOCATE('@', email) AS at_position FROM customers;

email	at_position
rahim@gmail.com	6
karim@yahoo.com	6
jannat@gmail.com	7
sakib@outlook.com	6
nadia@gmail.com	6

09

Words Inside Columns

১০ মিনিট

SELECT customer_name, LOCATE('Rahim', customer_name) AS position FROM customers;

Exists → position · Beginning / middle / end · Missing → 0

10

Not Found → 0

১০ মিনিট

SELECT LOCATE('X', 'HELLO'); -- 0 SELECT LOCATE('Python', 'SQL is powerful'); -- 0

Found? YES → Position · NO → 0 (NULL নয় — 0)

11

Start Position

১৫ মিনিট

LOCATE(search_string, text, start_position)

SELECT LOCATE('A', 'BANANA', 3);

4

BANANA 123456 ↑ start at 3 → next A at 4

12

Start Position Practice

১০ মিনিট

SELECT LOCATE('A', 'BANANA', 1); -- 2 SELECT LOCATE('A', 'BANANA', 3); -- 4 SELECT LOCATE('A', 'BANANA', 5); -- 6 SELECT LOCATE('A', 'BANANA', 7); -- 0

13

Email Analysis

১৫ মিনিট

SELECT customer_id, email, LOCATE('@', email) AS at_position FROM customers; SELECT email, LOCATE('.', email) AS dot_position FROM customers;

শুধু position — domain extract অন্য module।

14

Product Code Analysis

১০ মিনিট

SELECT product_code, LOCATE('-', product_code) AS first_dash_position FROM products; SELECT product_code, LOCATE('-', product_code, 4) AS next_dash_position FROM products;

15

URL Case Study

১০ মিনিট

SELECT url, LOCATE('://', url) AS protocol_position FROM websites;

https://google.com → 6 https://youtube.com → 6 http://example.com → 5

16

Alias (সংক্ষেপ)

৫ মিনিট

SELECT email, LOCATE('@', email) AS at_position FROM customers;

at_position = readable output name।

17

Case / Collation Note

১০ মিনিট

Upper/lowercase match collation-এর উপর নির্ভর করতে পারে। Unexpected হলে collation check — গভীর lesson নয়।

18

Predict the Output (২০)

১৫ মিনিট

Predict: LOCATE('a','Bangladesh')

2

Predict: LOCATE('B','Bangladesh')

1

Predict: LOCATE('desh','Bangladesh')

7

Predict: LOCATE('SQL','I love SQL')

8

Predict: LOCATE('x','Hello')

0

Predict: LOCATE('A','BANANA')

2

Predict: LOCATE('A','BANANA',3)

4

Predict: LOCATE('A','BANANA',5)

6

Predict: LOCATE('A','BANANA',7)

0

Predict: LOCATE('@','rahim@gmail.com')

6

Predict: LOCATE('L','HELLO')

3

Predict: LOCATE('Data','Data Science')

1

Predict: LOCATE('Science','Data Science')

6

Predict: LOCATE('-','BD-DHK-001')

3

Predict: LOCATE('-','BD-DHK-001',4)

7

Predict: LOCATE('://','https://x.com')

6

Predict: LOCATE('://','http://x.com')

5

Predict: LOCATE('N','BANANA')

3

Predict: LOCATE('love','I love SQL')

3

Predict: LOCATE('Rahim','Karim Hossain')

0

19

Find the Mistake (১০)

১০ মিনিট

Bug: LOCATE(email, '@')

Args reversed — search first, then text

Bug: LOCATE('@' email)

Missing comma

Bug: LOCATE('@', 'email')

Quotes → literal 'email', not column

Bug: Expect NULL when not found

Returns 0

Bug: Think positions start at 0

Start at 1

Bug: Want 2nd A but no start

Use third arg start_position

Bug: LOCATE('@',email start 2)

Missing comma before start

Bug: Search from wrong start

Check start index

Bug: Confuse first vs later

Default = first only

Bug: Case surprise

Check collation — shallow warning

20

Common LOCATE Mistakes

৮ মিনিট

Arguments উল্টো
Comma ভুলে
Search text quotes ভুলে
Column quotes-এ
Position 0 থেকে ভাবা
Not found = NULL ভাবা
0 ভুলে যাওয়া
Repeated text গুলিয়ে
Start position না বোঝা
Wrong start
First vs later গুলিয়ে
Collation/case ignore

Pattern

Wrong → Why? → Correct → Expected

21

LOCATE Workflow

৫ মিনিট

LOCATE → Search Text → Target Text → Optional Start → Search → Found? Position : 0 LOCATE = Find something → Return where it starts

22

Business Cases (৮)

১০ মিনিট

Email — find @
Website — find ://
Product — find -
Order — find prefix marker
Employee — find dept marker
Customer ref — find pattern
Address — find a word
DQ — marker exists? where?

SELECT value, LOCATE(marker, value) AS pos FROM business_data;

23

Mini Project — Text Position Analyzer

১৫ মিনিট

CREATE TABLE records ( record_id INT, record_type VARCHAR(50), record_value VARCHAR(200) ); -- 20+ emails, URLs, product/order/employee codes SELECT record_id, record_type, record_value, LOCATE('@', record_value) AS at_pos, LOCATE('-', record_value) AS dash_pos, LOCATE('://', record_value) AS protocol_pos FROM records;

@ in emails
- in product codes
:// in URLs
Word in names
First repeated char
Later occurrence via start
Not found = 0
Position-analysis report

24

Professional Use

৬ মিনিট

Raw → Inspect → LOCATE → Pattern Position → DQ → Analysis Analyst · Engineer · BI · DB Dev · DS — text position inspection

25

Performance Notes

৫ মিনিট

LOCATE text scan করে
Millions row-এ cost আছে
অপ্রয়োজনীয় বারবার হিসাব এড়াও
ETL-এ একবার parse vs report-এ বারবার

26

Pattern Library

৬ মিনিট

LOCATE('@', email)
LOCATE('SQL', description)
LOCATE('-', product_code)
LOCATE('-', product_code, 5)
LOCATE('://', url)
LOCATE('Rahim', customer_name)
LOCATE('@', email) AS at_position
Not found → 0

27

Classroom Activities (১৫)

১০ মিনিট

Act 1 — Find @

LOCATE('@', email)

Act 2 — First A in BANANA

2

Act 3 — Find word SQL

LOCATE('SQL', sentence)

Act 4 — Find separator -

LOCATE('-', code)

Act 5 — Repeated char

First occurrence default

Act 6 — Start position

LOCATE('A','BANANA',3)→4

Act 7 — Predict 0

Missing text → 0

Act 8 — Fix reversed args

LOCATE(search, text)

Act 9 — Fix missing comma

Add , between args

Act 10 — Fix quoted column

Remove quotes around column

Act 11 — Email data

at_position column

Act 12 — Product codes

first_dash_position

Act 13 — URLs

protocol_position

Act 14 — Employee codes

LOCATE('-', emp_code)

Act 15 — Position report

SELECT value, LOCATE(...) AS pos

28

Interview (৩০)

১২ মিনিট

IV 1. LOCATE কী?

Text ভিতরে text খুঁজে position · ইউজ: @/-

IV 2. Return কী?

Starting position বা 0

IV 3. Basic syntax?

LOCATE(search, text)

IV 4. First arg?

যা খুঁজছি

IV 5. Second arg?

যেখানে খুঁজছি

IV 6. Third arg?

Optional start_position

IV 7. Character?

LOCATE('a', text)

IV 8. Word?

LOCATE('SQL', text)

IV 9. Not found?

0

IV 10. Why 0?

No match

IV 11. Start 0 or 1?

1

IV 12. Repeated text?

First occurrence

IV 13. Later occurrence?

Third argument

IV 14. With column?

LOCATE('@', email)

IV 15. Find @?

LOCATE('@', email)

IV 16. Find -?

LOCATE('-', code)

IV 17. Find ://?

LOCATE('://', url)

IV 18. Data quality?

Marker exists? where?

IV 19. Common mistake?

Reversed args

IV 20. Quoted column?

Literal not column

IV 21. Reversed args?

Wrong search

IV 22. LOCATE('X','HELLO')?

0

IV 23. Start position?

Search from index

IV 24. Start past end?

0

IV 25. Structured strings?

Find separators

IV 26. Analysts?

Text profiling

IV 27. Engineers?

ETL validation

IV 28. Collation?

Case may depend — check

IV 29. Business example?

Email @ position

IV 30. One sentence?

Find where search starts inside text

29

MCQ (২৫)

১০ মিনিট

MCQ 1. LOCATE returns? A) text B) position/0 C) table D) bytes

B

MCQ 2. Arg order? A) text, search B) search, text C) only one D) random

B

MCQ 3. LOCATE('L','HELLO')? A) 1 B) 3 C) 4 D) 0

B

MCQ 4. Not found? A) NULL B) -1 C) 0 D) error

C

MCQ 5. Positions start? A) 0 B) 1 C) -1 D) 2

B

MCQ 6. First A in BANANA? A) 2 B) 4 C) 6 D) 0

A

MCQ 7. LOCATE('A','BANANA',3)? A) 2 B) 4 C) 6 D) 0

B

MCQ 8. LOCATE('@','rahim@gmail.com')? A) 5 B) 6 C) 7 D) 0

B

MCQ 9. LOCATE('-','BD-DHK')? A) 1 B) 2 C) 3 D) 0

C

MCQ 10. LOCATE('://','https://x')? A) 5 B) 6 C) 7 D) 0

B

MCQ 11. Can search words? A) no B) yes C) only @ D) only -

B

MCQ 12. LOCATE(email,'@') wrong why?

Args reversed

MCQ 13. LOCATE('@','email') measures?

Literal word email

MCQ 14. Third arg use?

Start search later

MCQ 15. Start past end?

0

MCQ 16. Alias for?

Readable column name

MCQ 17. Focus of class? A) LENGTH B) LOCATE C) CONCAT D) JOIN

B

MCQ 18. Default occurrence? A) last B) first C) all D) random

B

MCQ 19. Collation note? A) ignore B) may affect case match C) always error D) drops DB

B

MCQ 20. DQ use? A) find marker position B) DROP C) AVG D) UNION

A

MCQ 21. LOCATE('x','Hello')? A) 1 B) 5 C) 0 D) NULL

C

MCQ 22. LOCATE('Data','Data Science')? A) 0 B) 1 C) 5 D) 6

B

MCQ 23. LOCATE('Science','Data Science')? A) 1 B) 5 C) 6 D) 0

C

MCQ 24. Missing comma? A) OK B) syntax error C) 0 D) auto

B

MCQ 25. Mental model?

Search + target → position or 0

30

Viva (২০)

৮ মিনিট

Viva 1. LOCATE কী?

Position finder

Viva 2. Return?

Position or 0

Viva 3. Position start?

1

Viva 4. Not found?

0

Viva 5. First A BANANA?

2

Viva 6. Start position কেন?

Later occurrence

Viva 7. Email @?

LOCATE('@', email)

Viva 8. Args order?

search then text

Viva 9. Word search?

হ্যাঁ

Viva 10. Repeated?

First only

Viva 11. Column?

LOCATE('@', email)

Viva 12. Quoted column?

Literal

Viva 13. Reversed?

Wrong

Viva 14. URL ://?

LOCATE('://', url)

Viva 15. Product -?

LOCATE('-', code)

Viva 16. Start=7 BANANA A?

0

Viva 17. Alias?

AS at_position

Viva 18. Collation?

Case may vary

Viva 19. DQ?

Marker where/exists

Viva 20. One line?

Where search starts in text

31

Exercises (২০)

১২ মিনিট

Ex 1 — a in Bangladesh

LOCATE('a','Bangladesh')→2

Ex 2 — SQL in sentence

LOCATE('SQL','I love SQL')

Ex 3 — @ in email

LOCATE('@', email)

Ex 4 — - in product code

LOCATE('-', product_code)

Ex 5 — Word in sentence

LOCATE('love', text)

Ex 6 — Table column

SELECT email, LOCATE('@',email)

Ex 7 — Repeated char

LOCATE('A','BANANA')

Ex 8 — Start position

LOCATE('A','BANANA',3)

Ex 9 — URL marker

LOCATE('://', url)

Ex 10 — Emp code sep

LOCATE('-', emp_code)

Ex 11 — Email analysis

at_position report

Ex 12 — Product codes

first/next dash

Ex 13 — Order codes

LOCATE('-', order_code)

Ex 14 — URLs

protocol_position

Ex 15 — Missing patterns

Result 0 rows/values

Ex 16 — Later occurrences

Third argument

Ex 17 — Position report

Multi LOCATE columns

Ex 18 — Debug reverse

Swap args

Ex 19 — Predict outputs

Classroom quiz

Ex 20 — Mini project

records table analyzer

32

Cheat Sheet + Memory Map

৫ মিনিট

LOCATE() → find search inside text → starting position
LOCATE('a', 'Bangladesh')
LOCATE('SQL', 'I love SQL')
LOCATE('@', email)
LOCATE('A', 'BANANA', 3)
Not found → 0
Positions: 1, 2, 3, ...

LOCATE → Search Something → Inside Text
→ Found? Position : 0

LOCATE() = এক text-এর ভিতরে আরেক text কোথা থেকে শুরু হয়েছে সেটা খুঁজে বের করা।

CONCAT / LENGTH / SUBSTRING / INSTR / LIKE — অন্য module-এর বিষয়।

Text কোথায়? — SQL LOCATE()

বিষয়সূচী

কেন LOCATE()?

Real-life Stories

Position বোঝা

LOCATE() কী?

Simple Examples

Character vs Word

First Occurrence

Table Columns

Words Inside Columns

Not Found → 0

Start Position

Start Position Practice

Email Analysis

Product Code Analysis

URL Case Study

Alias (সংক্ষেপ)

Case / Collation Note

Predict the Output (২০)

Find the Mistake (১০)

Common LOCATE Mistakes

LOCATE Workflow

Business Cases (৮)

Mini Project — Text Position Analyzer

Professional Use

Performance Notes

Pattern Library

Classroom Activities (১৫)

Interview (৩০)

MCQ (২৫)

Viva (২০)

Exercises (২০)

Cheat Sheet + Memory Map