MySQL SUBSTR

The SUBSTR function in MySQL is used to extract a substring from a given string. It allows you to retrieve a specific portion of a string based on a specified starting position and, optionally, a specified length.

Syntax

Here is the syntax for the SUBSTR function in MySQL:

SUBSTR(str, start[, length])

str: This is the input string from which you want to extract a substring.
start: This is the starting position within the input string. It can be a positive integer indicating the position from the beginning of the string, or a negative integer indicating the position from the end of the string.
length (optional): This parameter defines the length of the substring to be extracted. If omitted, the function will return all characters from the starting position to the end of the string.

Examples

Here are some examples of how to use the SUBSTR function in MySQL:

1. Extracting the first three characters of a string:

SELECT SUBSTR('Hello, world!', 1, 3);

Output: Hel

2. Extracting the last two characters of a string:

SELECT SUBSTR('Hello, world!', -2);

Output: o!

3. Extracting a substring from a specific position:

SELECT SUBSTR('Hello, world!', 6);

Output: world!

4. Extracting a substring with negative start position:

SELECT SUBSTR('Hello, world!', -6);

Output: world!

5. Extracting specific parts of text fields from tables:

SELECT SUBSTR(name, 1, 2) AS first_two_chars
FROM users;

6. Filtering records based on substring patterns:

SELECT *
FROM products
WHERE name LIKE '%chocolate%';

7. Modifying data by replacing or removing substrings:

UPDATE products
SET name = REPLACE(name, 'chocolate', 'candy');

The SUBSTR function is an essential tool for working with strings in MySQL, providing flexibility and precision in extracting, manipulating, and analyzing text data within database queries.