Intermediate

22 min read

SQL String Functions — UCASE, LCASE, MID, LENGTH, LEFT, RIGHT, INSTR, TRIM

Master SQL String Functions for CBSE Class 12 Informatics Practices. Covers UCASE UPPER LCASE LOWER for case conversion, LENGTH to count characters including spaces, MID SUBSTRING SUBSTR with 1-based position extraction, LEFT and RIGHT for extracting from string ends, INSTR to find position of substring returning 0 when not found, LTRIM RTRIM TRIM to remove whitespace, complete function summary table with verified output prediction questions.

New

SQL (Structured Query Language) is not just a tool for storing and retrieving data — it also provides a powerful set of built-in functions that let you manipulate and transform data directly within your queries, without needing a separate programming language. String functions are among the most tested SQL topics in CBSE Class 12 Informatics Practices, appearing in virtually every board paper — as output-prediction questions, query-writing questions, and MCQs. They let you convert case, extract substrings, measure length, find positions, and clean up whitespace — all inside a SELECT statement. This note covers all string functions from the official CBSE IP 2025–26 syllabus with verified outputs for every example.

1. Introduction to SQL Functions

SQL functions are built-in operations that take input value(s) and return a result. They are classified as:

Category	Operates on	Returns	Examples
Single-Row Functions	One row at a time	One result per row	UCASE(), LENGTH(), MID(), ROUND()
Aggregate Functions	Group of rows	One result per group	MAX(), MIN(), AVG(), SUM(), COUNT()

Single-row functions are further divided into: String (Text) functions, Math (Numeric) functions, and Date functions. This note covers all String Functions from the CBSE IP syllabus.

We will use this sample table throughout all examples:

Table: STUDENT

RollNo	Name	City	Marks
1	Aryan	Delhi	85
2	Priya	Mumbai	92
3	Rohan	Delhi	78
4	Sneha	Pune	95

2. Case Conversion Functions — UCASE() / UPPER() and LCASE() / LOWER()

Function	Syntax	Description
UCASE() / UPPER()	`UCASE(string)`	Converts all characters to UPPERCASE
LCASE() / LOWER()	`LCASE(string)`	Converts all characters to lowercase

SELECT UCASE(Name), LCASE(City) FROM STUDENT;

UCASE(Name)	LCASE(City)
ARYAN	delhi
PRIYA	mumbai
ROHAN	delhi
SNEHA	pune

-- Direct value (no table needed)
SELECT UCASE('hello world');   -- HELLO WORLD
SELECT LCASE('PYTHON SQL');    -- python sql

Note: UCASE() and UPPER() are synonyms — they do the same thing. Same for LCASE() and LOWER(). Use either in board answers.

3. LENGTH() — String Length

LENGTH(string) returns the number of characters in a string, including spaces.

SELECT Name, LENGTH(Name) FROM STUDENT;

Name	LENGTH(Name)
Aryan	5
Priya	5
Rohan	5
Sneha	5

SELECT LENGTH('Database');    -- 8
SELECT LENGTH('SQL');          -- 3
SELECT LENGTH('  hi  ');       -- 6 (spaces are counted)

4. MID() / SUBSTRING() / SUBSTR() — Extract Substring

These three functions are synonyms — they extract a portion of a string.

MID(string, position, length)

position: Starting position — 1-based (first character = position 1, not 0).
length: Number of characters to extract.

SELECT MID('Informatics', 1, 6);    -- Inform
SELECT MID('Informatics', 5, 3);    -- rma
SELECT SUBSTR('Database', 2, 4);     -- atab
SELECT SUBSTRING('Computer', 3, 5);  -- mpute

Verification — MID('Informatics', pos, len):

I  n  f  o  r  m  a  t  i  c  s
1  2  3  4  5  6  7  8  9  10 11
MID('Informatics', 1, 6) → positions 1–6 → I,n,f,o,r,m → Inform
MID('Informatics', 5, 3) → positions 5–7 → r,m,a → rma

-- On table column
SELECT Name, MID(Name, 1, 3) AS ShortName FROM STUDENT;

Name	ShortName
Aryan	Ary
Priya	Pri
Rohan	Roh
Sneha	Sne

5. LEFT() and RIGHT() — Extract from Ends

Function	Syntax	Returns	Example	Output
LEFT()	`LEFT(str, n)`	First n characters from the left	`LEFT('Computer', 3)`	`Com`
RIGHT()	`RIGHT(str, n)`	Last n characters from the right	`RIGHT('Computer', 4)`	`uter`

SELECT LEFT('Informatics', 4);    -- Info
SELECT RIGHT('Informatics', 5);   -- atics
SELECT LEFT(City, 3) FROM STUDENT;
-- Del, Mum, Del, Pun

Relationship: LEFT(str, n) = MID(str, 1, n) and RIGHT(str, n) = MID(str, LENGTH(str)-n+1, n) — they are shortcuts.

6. INSTR() — Find Position of a Substring

INSTR(string, substring) returns the 1-based position of the first occurrence of substring within string. Returns 0 if not found.

SELECT INSTR('Computer Science', 'Science');  -- 10
SELECT INSTR('hello', 'world');            -- 0 (not found)
SELECT INSTR('Informatics', 'mat');       -- 6

Verification — INSTR('Computer Science', 'Science'):

C  o  m  p  u  t  e  r     S  c  i  e  n  c  e
1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 16
'Science' starts at position 10 → INSTR = 10

7. LTRIM(), RTRIM() and TRIM() — Remove Whitespace

Function	Removes	Example	Output
LTRIM()	Leading spaces (left side)	`LTRIM(' hello ')`	`'hello '`
RTRIM()	Trailing spaces (right side)	`RTRIM(' hello ')`	`' hello'`
TRIM()	Both leading and trailing spaces	`TRIM(' hello ')`	`'hello'`

-- LENGTH before and after trim
SELECT LENGTH('  hi  ');             -- 6
SELECT LENGTH(TRIM('  hi  '));       -- 2
SELECT LENGTH(LTRIM('  hi  '));      -- 4 (only left spaces removed)
SELECT LENGTH(RTRIM('  hi  '));      -- 4 (only right spaces removed)

Important use case: TRIM functions are commonly used to clean up data — removing accidental leading/trailing spaces in names, emails, or other text fields before comparison or display.

8. Complete String Functions Summary

Function	Syntax	Purpose	Quick Example → Output
UCASE() / UPPER()	`UCASE(str)`	Uppercase	`UCASE('hello')` → `HELLO`
LCASE() / LOWER()	`LCASE(str)`	Lowercase	`LCASE('HELLO')` → `hello`
LENGTH()	`LENGTH(str)`	Count characters	`LENGTH('SQL')` → `3`
MID() / SUBSTR() / SUBSTRING()	`MID(str, pos, len)`	Extract substring (1-based)	`MID('Python', 2, 4)` → `ytho`
LEFT()	`LEFT(str, n)`	First n characters	`LEFT('Python', 3)` → `Pyt`
RIGHT()	`RIGHT(str, n)`	Last n characters	`RIGHT('Python', 3)` → `hon`
INSTR()	`INSTR(str, sub)`	Position of substring (1-based, 0 if absent)	`INSTR('SQL', 'Q')` → `2`
LTRIM()	`LTRIM(str)`	Remove leading spaces	`LTRIM(' hi')` → `'hi'`
RTRIM()	`RTRIM(str)`	Remove trailing spaces	`RTRIM('hi ')` → `'hi'`
TRIM()	`TRIM(str)`	Remove both leading and trailing spaces	`TRIM(' hi ')` → `'hi'`

SQL String Functions — Output Prediction Cheatsheet

Query	Output	Key point
`SELECT LENGTH(' SQL ');`	`7`	Spaces are counted in LENGTH
`SELECT MID('Python', 1, 3);`	`Pyt`	Position is 1-based (not 0)
`SELECT MID('Python', 2, 4);`	`ytho`	Start at 'y' (pos 2), take 4 chars
`SELECT INSTR('abcabc', 'b');`	`2`	Returns FIRST occurrence only
`SELECT INSTR('hello', 'xyz');`	`0`	Returns 0 if substring not found
`SELECT LENGTH(TRIM(' hi '));`	`2`	TRIM removes spaces before LENGTH counts
`SELECT LEFT('Informatics', 4);`	`Info`	Same as MID('Informatics', 1, 4)
`SELECT RIGHT('Informatics', 5);`	`atics`	Last 5 characters from right

Board Exam Power Tips — Avoid These Common Mistakes

MID() uses 1-based positions, NOT 0-based. MID('Python', 1, 3) starts at 'P' (position 1) and gives Pyt. A very common error is treating it like Python slicing and answering yth (which would be positions 2–4 in Python). Always count from 1 in SQL.
INSTR() returns 0 when not found, not -1 like Python's find(). Board MCQs frequently test this. Also, INSTR returns the position of the first occurrence only.
LENGTH() counts spaces. LENGTH(' hi ') = 6, not 2. To get the length without spaces, use LENGTH(TRIM(str)).
UCASE/UPPER and LCASE/LOWER are synonyms — both forms are valid in board answers. Similarly, MID, SUBSTRING, and SUBSTR are all synonyms and can be used interchangeably.
LTRIM removes only leading spaces; RTRIM removes only trailing spaces; TRIM removes both. A common mistake is thinking LTRIM removes spaces from the left within the string — it only removes spaces at the beginning of the string.
String functions do NOT modify the actual table data — they only transform data in the query output. The underlying table remains unchanged. If you want to permanently modify stored data, you would use UPDATE with a string function, not just SELECT.
Case sensitivity: In MySQL (used by CBSE), string comparisons in WHERE clauses are case-insensitive by default. However, UCASE/LCASE in SELECT change the displayed output — the table values themselves are unchanged.

Practice Questions (CBSE Board Level)

Q1 (2 marks): Write the output of the following SQL queries:

(i)  SELECT UCASE('informatics practices');
(ii) SELECT LENGTH('Computer Science');

Explanation:

(i) INFORMATICS PRACTICES — The UCASE() function converts all alphabetical characters to uppercase.

(ii) 16 — The LENGTH() function counts all characters in the string, including the space between 'Computer' and 'Science'.

Q2 (2 marks): Write the output of the following SQL queries:

(i)  SELECT MID('Informatics', 1, 6);
(ii) SELECT MID('Informatics', 5, 3);

Explanation:

(i) Inform
MID('Informatics', 1, 6) — Start at position 1 ('I') and extract exactly 6 characters: I-n-f-o-r-m = Inform.

(ii) rma
MID('Informatics', 5, 3) — Start at position 5 ('r') and extract exactly 3 characters: r-m-a = rma.

Q3 (1 mark): What will the following query return?

SELECT INSTR('Computer Science', 'Science');

Output: 10

Explanation: The INSTR() function returns the 1-based starting position of the first occurrence of a substring. Counting through 'Computer Science': C(1) o(2) m(3) p(4) u(5) t(6) e(7) r(8) [space](9) S(10) — the word 'Science' begins precisely at position 10.

Q4 (2 marks): Write SQL queries to:

(i) Display the first 3 characters of each student's Name from the STUDENT table.

(ii) Display names in uppercase where City is 'Delhi'.

Explanation:

(i) SELECT LEFT(Name, 3) FROM STUDENT;
-- OR: SELECT MID(Name, 1, 3) FROM STUDENT;

(ii) SELECT UCASE(Name) FROM STUDENT WHERE City = 'Delhi';

Output of (ii) based on hypothetical data:

UCASE(Name)
ARYAN
ROHAN

Q5 (2 marks): Write the output of the following queries:

(i)  SELECT LENGTH(TRIM('   SQL   '));
(ii) SELECT RIGHT('Informatics', 5);

Explanation:

(i) 3
The nested functions evaluate from the inside out. First, TRIM(' SQL ') removes all leading and trailing spaces, leaving just 'SQL'. Then, LENGTH('SQL') counts the remaining characters, which is 3.

(ii) atics
RIGHT('Informatics', 5) — Extracts the last 5 characters of 'Informatics' starting from the right side: a-t-i-c-s = atics.

Q6 (1 mark — MCQ): What is the output of SELECT INSTR('abcdef', 'xyz');?

A) NULL
B) -1
C) 0
D) Error

Answer: C) 0.

Explanation: In SQL, the INSTR() function returns 0 when the requested substring is not found within the string. It does not return NULL or -1 (unlike some programming languages such as Python or JavaScript, where .find() or .indexOf() returns -1).

Q7 (3 marks): Consider the following table EMPLOYEE:

EmpID	EmpName	Department
E01	Ramesh Kumar	Finance
E02	Sunita Singh	IT
E03	Anil Sharma	Finance

Write SQL queries for:

(i) Display EmpName in uppercase.

(ii) Display the first 5 characters of each EmpName.

(iii) Display the length of each EmpName.

Explanation:

(i)  SELECT UCASE(EmpName) FROM EMPLOYEE;

(ii) SELECT LEFT(EmpName, 5) FROM EMPLOYEE;
     -- OR: SELECT MID(EmpName, 1, 5) FROM EMPLOYEE;

(iii) SELECT EmpName, LENGTH(EmpName) FROM EMPLOYEE;

Output of (iii):

EmpName	LENGTH(EmpName)
Ramesh Kumar	12
Sunita Singh	12
Anil Sharma	11

Note: The LENGTH() function counts all characters including the spaces. For example, "Ramesh Kumar" has 6 letters in the first name, 1 space, and 5 letters in the last name, giving a total length of 12.

Related Study Material

More Informatics practices Notes More Database Query Using Sql Notes All Sql Functions Topics

Practice Questions

Informatics practices Practice Sql Functions Questions

Topic Information

DifficultyIntermediate

Est. Read Time22 minutes

CategorySql Functions

Quick Actions

Track Your Learning

Study Tips

Take notes of key points while reading

Practice related questions after studying

Review topics marked as "Learning" regularly

Test your knowledge with practice questions

Edvaya Target

Detailed Notes & Solved Examples

Master complex concepts with our rich, topic-wise study notes. Access hundreds of step-by-step solved examples and interactive flashcards right from your phone.

Edvaya Target

Edvaya Aspire

Take a Free Mock Test

Target Subjects

Aspire Subjects

Subject Mastery

SQL String Functions — UCASE, LCASE, MID, LENGTH, LEFT, RIGHT, INSTR, TRIM

1. Introduction to SQL Functions

2. Case Conversion Functions — UCASE() / UPPER() and LCASE() / LOWER()

3. LENGTH() — String Length

4. MID() / SUBSTRING() / SUBSTR() — Extract Substring

Verification — MID('Informatics', pos, len):

5. LEFT() and RIGHT() — Extract from Ends

6. INSTR() — Find Position of a Substring

Verification — INSTR('Computer Science', 'Science'):

7. LTRIM(), RTRIM() and TRIM() — Remove Whitespace

8. Complete String Functions Summary

SQL String Functions — Output Prediction Cheatsheet

Practice Questions (CBSE Board Level)

Related Study Material

Practice Questions

Topic Information

Quick Actions

Track Your Learning

Study Tips

Edvaya Target

We Value Your Privacy