This lecture covers common SQL string functions. Data often comes in text format, and sometimes you need to clean it up, extract parts of it, or change its appearance. SQL provides a rich set of string functions to help you manipulate text data directly within your queries.
Common String Functions
Let’s explore some of the most frequently used string functions in SQLite.
`LENGTH(string)`
Returns the number of characters in a string.
SELECT LENGTH('Hello World'); -- Result: 11
SELECT LENGTH(StudentName) FROM Students WHERE StudentID = 1; -- Length of 'Alice Brown'`SUBSTR(string, start, length)` or `SUBSTRING(string, start, length)`
Extracts a substring from a string. `start` is the starting position (1-based), and `length` is the number of characters to extract.
SELECT SUBSTR('Hello World', 1, 5); -- Result: 'Hello'
SELECT SUBSTR('Hello World', 7); -- Result: 'World' (if length is omitted, extracts to end)
SELECT SUBSTR(Email, INSTR(Email, '@') + 1) AS Domain FROM Instructors WHERE InstructorID = 1;
-- This would extract 'university.edu' from 'smith@university.edu'Note: `INSTR(string, substring)` returns the starting position of the first occurrence of `substring` within `string`.
`UPPER(string)` and `LOWER(string)`
Converts a string to uppercase or lowercase.
SELECT UPPER('hello'); -- Result: 'HELLO'
SELECT LOWER('WORLD'); -- Result: 'world'
SELECT UPPER(StudentName) FROM Students;`REPLACE(string, old_substring, new_substring)`
Replaces all occurrences of a specified substring with another substring.
SELECT REPLACE('Hello World', 'World', 'SQL'); -- Result: 'Hello SQL'
SELECT REPLACE(Email, 'university.edu', 'example.com') FROM Instructors WHERE InstructorID = 1;`TRIM(string)`, `LTRIM(string)`, `RTRIM(string)`
Removes leading and/or trailing spaces (or other specified characters) from a string.
SELECT TRIM(' Hello World '); -- Result: 'Hello World'
SELECT LTRIM(' Hello'); -- Result: 'Hello'
SELECT RTRIM('World '); -- Result: 'World'`||` (Concatenation Operator)
Combines two or more strings together.
SELECT 'First' || ' ' || 'Last'; -- Result: 'First Last'
SELECT FirstName || ' ' || LastName AS FullName FROM Contacts;Why are these useful?
String functions are essential for:
- Data Cleaning: Removing extra spaces, standardizing case.
- Data Formatting: Presenting data in a specific way for reports.
- Data Extraction: Pulling out specific parts of a text field (like a domain from an email).
In the Next Lecture…
We will shift our focus to numbers and explore various numeric functions that allow us to perform calculations, rounding, and other mathematical operations on numerical data.
Lecture 45 – Filtering Groups with Statements
