SQL String Functions (Part 2)

SQL provides several string functions that allow us to extract, search, and remove unwanted spaces from text values. These functions are widely used while processing names, addresses, product codes, and other text data stored in database tables.

In this article, we will study the remaining CBSE-prescribed string functions:

• MID()
• SUBSTRING()
• SUBSTR()
• INSTR()
• LTRIM()
• RTRIM()
• TRIM()

---

Sample Table

EmpID	Name	Department
101	Amit Sharma	Sales
102	Neha Gupta	Finance
103	Rohan Verma	HR

---

1. MID()

Syntax

MID(string, start_position, length)

Example

SELECT MID('COMPUTER',2,4);

Output

OMPU

---

2. SUBSTRING()

Syntax

SUBSTRING(string, start_position, length)

Example

SELECT SUBSTRING('DATABASE',3,4);

Output

TABA

---

3. SUBSTR()

SUBSTR() works similarly to SUBSTRING() in MySQL.

Syntax

SUBSTR(string, start_position, length)

Example

SELECT SUBSTR('INFORMATICS',1,6);

Output

INFORM

---

4. INSTR()

Syntax

INSTR(string, substring)

Example

SELECT INSTR('DATABASE','BASE');

Output

5

The word BASE starts from the 5th character.

---

5. LTRIM()

Syntax

LTRIM(string)

Example

SELECT LTRIM('     CBSE');

Output

CBSE

---

6. RTRIM()

Syntax

RTRIM(string)

Example

SELECT RTRIM('CBSE      ');

Output

CBSE

---

7. TRIM()

Syntax

TRIM(string)

Example

SELECT TRIM('     SQL     ');

Output

SQL

---

Comparison of String Functions

Function	Purpose	Example
MID()	Extract characters from middle	MID('Python',2,3)
SUBSTRING()	Extract substring	SUBSTRING('Python',3,4)
SUBSTR()	Extract substring	SUBSTR('Python',3,4)
INSTR()	Find position of substring	INSTR('Python','th')
LTRIM()	Remove left spaces	LTRIM(' SQL')
RTRIM()	Remove right spaces	RTRIM('SQL ')
TRIM()	Remove both-side spaces	TRIM(' SQL ')

---

Practical Examples Using a Table

SELECT Name,
       SUBSTRING(Name,1,4)
FROM Employee;

---

SELECT Name,
       INSTR(Name,'a')
FROM Employee;

---

SELECT TRIM(Name)
FROM Employee;

---

Real-Life Applications

Function	Application
MID()	Extract roll number digits.
SUBSTRING()	Extract area code from phone number.
INSTR()	Find special characters or symbols.
LTRIM()	Remove unwanted leading spaces.
RTRIM()	Clean imported data.
TRIM()	Standardize database values.

---

Common Errors

Error	Reason
Incorrect Start Position	Position outside the string length.
Syntax Error	Incorrect brackets or commas.
Unknown Column	Wrong column name.

---

Quick Revision

Function	Purpose
MID()	Extract middle characters
SUBSTRING()	Extract substring
SUBSTR()	Extract substring
INSTR()	Find position
LTRIM()	Remove left spaces
RTRIM()	Remove right spaces
TRIM()	Remove both-side spaces

---

CBSE Exam Tips

• MID(), SUBSTRING(), and SUBSTR() are used to extract characters from a string.
• INSTR() returns the position of the first occurrence of a substring.
• LTRIM() removes spaces from the left side only.
• RTRIM() removes spaces from the right side only.
• TRIM() removes spaces from both the beginning and the end of a string.
• Practice extracting text using different starting positions and lengths.

---

Summary

Advanced SQL string functions help extract, search, and clean text stored in databases. Functions such as MID(), SUBSTRING(), SUBSTR(), INSTR(), LTRIM(), RTRIM(), and TRIM() are essential for processing textual data efficiently. These functions are frequently used in database applications and are important for the CBSE Class 12 Informatics Practices theory and practical examinations.