SQLite is renowned for being a light and fast database engine suitable for small-scale applications, often residing directly on the device's file system. One of its key features is the ability to handle string operations efficiently. In this article, we will delve into some useful string functions in SQLite, starting from the basics and moving to advanced usages.
Basic String Functions
1. || - Concatenation Operator
This operator allows you to concatenate two or more strings. It's incredibly simple and effective.
SELECT 'Hello ' || 'World';The above SQL statement will result in Hello World.
2. LENGTH()
The LENGTH() function returns the number of characters in a string.
SELECT LENGTH('SQLite3');This will return 7 as there are 7 characters in 'SQLite3'.
3. UPPER() and LOWER()
These functions are used to convert text to uppercase or lowercase.
SELECT UPPER('sqlite');Output: SQLITE
SELECT LOWER('SQLITE');Output: sqlite
Intermediate String Functions
4. TRIM(), LTRIM(), and RTRIM()
These functions are used to strip spaces or other specified characters from a string.
SELECT TRIM(' Lot of space ');Returns: 'Lot of space'
SELECT LTRIM(' Trim left');Returns: 'Trim left'
SELECT RTRIM('Trim right ');Returns: 'Trim right'
5. SUBSTR() or SUBSTRING()
This function extracts a substring from a given string based on your specified parameters.
SELECT SUBSTR('abcdef', 2, 3);Extracts 3 characters starting from position 2, resulting in 'bcd'.
Advanced String Functions
6. INSTR()
The INSTR() function is used to find the first occurrence of a substring in a given string. It returns the location as an integer value.
SELECT INSTR('Look here, there', 'here');This query returns 6 as 'here' starts at the 6th position.
7. REPLACE()
The REPLACE() function substitutes all occurrences of a specified substring with another substring.
SELECT REPLACE('Jingle Bells', 'Bells', 'Drums');Returns: 'Jingle Drums'.
8. LIKE and GLOB - Pattern Matching
While not exactly functions, LIKE and GLOB are critical for string pattern matching.
SELECT * FROM employees WHERE name LIKE 'A%';This query fetches all names starting with 'A'. The '%' is a wildcard character in LIKE.
While LIKE uses Unix shell uppercase translation rules, GLOB is more straightforward for exact matching.
SELECT * FROM documents WHERE title GLOB '*important*';This query searches titles containing the word 'important'.
Conclusion
Understanding and mastering these string functions enhances your querying capabilities in SQLite significantly. Whether you're performing basic operations like concatenation or complex pattern matching, SQLite provides powerful tools to manage string data efficiently.
