MCQ King

SQLite – 22 – String Functions

Start MCQ

SQLite offers a variety of string functions that allow you to manipulate and work with text and string data within your SQL queries. These functions can be used to perform tasks like string concatenation, substring extraction, text manipulation, and case conversion. In this discussion, we’ll explore some of the most commonly used string functions in SQLite, along with practical examples.

SQLite’s string functions can be broadly categorized into the following groups:

  1. String Manipulation Functions: Functions that manipulate the content of strings.
  2. Text Case Functions: Functions that convert text between different cases (e.g., uppercase to lowercase).
  3. String Length Functions: Functions that provide the length or character count of a string.
  4. String Trimming Functions: Functions that remove leading or trailing spaces from a string.

String Manipulation Functions:

  1. CONCAT: Combines two or more strings together. SELECT CONCAT('Hello', ' ', 'World'); -- Example result: 'Hello World'
  2. SUBSTR: Extracts a substring from a string. SELECT SUBSTR('SQLite is great', 8, 6); -- Example result: 'is gre'
  3. INSTR: Finds the position of a substring within a string. SELECT INSTR('SQLite is great', 'is'); -- Example result: 8
  4. REPLACE: Replaces all occurrences of a substring within a string. SELECT REPLACE('SQLite is great', 'is', 'was'); -- Example result: 'SQLite was great'

Text Case Functions:

  1. UPPER: Converts a string to uppercase. SELECT UPPER('SQLite is great'); -- Example result: 'SQLITE IS GREAT'
  2. LOWER: Converts a string to lowercase. SELECT LOWER('SQLite is great'); -- Example result: 'sqlite is great'
  3. INITCAP: Converts the first character of each word to uppercase. SELECT INITCAP('SQLite is great'); -- Example result: 'Sqlite Is Great'

String Length Functions:

  1. LENGTH: Returns the length of a string. SELECT LENGTH('SQLite is great'); -- Example result: 15
  2. CHAR_LENGTH: Returns the number of characters in a string. SELECT CHAR_LENGTH('SQLite is great'); -- Example result: 15
  3. OCTET_LENGTH: Returns the length of a string in bytes. SELECT OCTET_LENGTH('SQLite is great'); -- Example result: 15

String Trimming Functions:

  1. TRIM: Removes leading and trailing spaces or specified characters from a string. SELECT TRIM(' SQLite '); -- Example result: 'SQLite'
  2. LTRIM: Removes leading spaces or specified characters from a string. SELECT LTRIM(' SQLite '); -- Example result: 'SQLite '
  3. RTRIM: Removes trailing spaces or specified characters from a string. SELECT RTRIM(' SQLite '); -- Example result: ' SQLite'

Practical Examples:

Let’s consider some practical examples of using SQLite’s string functions. Suppose you have a table named Names with a column named FullName, and you want to extract the first name and last name separately:

SELECT FullName, SUBSTR(FullName, 1, INSTR(FullName, ' ') - 1) AS FirstName, SUBSTR(FullName, INSTR(FullName, ' ') + 1) AS LastName FROM Names;

In this query:

  • We use SUBSTR and INSTR to extract the first name and last name from the FullName column, assuming that names are separated by a single space.

Conclusion:

SQLite’s string functions provide a powerful set of tools for working with text and string data within your database. Whether you need to manipulate strings, convert text between cases, determine string lengths, or trim leading/trailing spaces, these functions offer the necessary functionality to perform these tasks efficiently. Understanding and utilizing these functions can help you write more versatile and expressive SQL queries when working with string data in SQLite databases.