2024-01-30 Substring methods in mySQL

[soraraso42]

Need to switch mindset where indexing in sql start at ONE!!!!!

1. SUBSTRING():

The SUBSTRING() function is used to extract a substring from a string.

Syntax:

SUBSTRING(string, start, length)

• string: The string from which to extract the substring.
• start: The position (index) at which to start extracting characters (1-based).
• length: Optional. The number of characters to extract. If omitted, the substring extends to the end of the string.

Example:

SELECT SUBSTRING('Hello World', 1, 5); -- Output: 'Hello'
SELECT SUBSTRING('Hello World', 7);    -- Output: 'World'

2. SUBSTRING_INDEX():

The SUBSTRING_INDEX() function returns a substring from a string before a specified number of occurrences of a delimiter.

Syntax:

SUBSTRING_INDEX(string, delimiter, count)

• string: The string to search within.
• delimiter: The delimiter used to separate parts of the string.
• count: The number of occurrences of the delimiter to consider. If positive, returns the substring before the count-th occurrence of the delimiter. If negative, returns the substring after the last count occurrences of the delimiter.

Example:

SELECT SUBSTRING_INDEX('apple,banana,cherry,orange', ',', 2); -- Output: 'apple,banana'
SELECT SUBSTRING_INDEX('apple,banana,cherry,orange', ',', -2); -- Output: 'cherry,orange'

3. LEFT():

The LEFT() function returns a specified number of characters from the left side of a string.

Syntax:

LEFT(string, length)

• string: The string from which to extract characters.
• length: The number of characters to extract from the left side of the string.

Example:

SELECT LEFT('Hello World', 5); -- Output: 'Hello'

4. RIGHT():

The RIGHT() function returns a specified number of characters from the right side of a string.

Syntax:

RIGHT(string, length)

• string: The string from which to extract characters.
• length: The number of characters to extract from the right side of the string.

Example:

SELECT RIGHT('Hello World', 5); -- Output: 'World'

5. MID() / SUBSTR():

The MID() function (also known as SUBSTR() in MySQL) extracts a substring from a string.

Syntax:

MID(string, start, length)
SUBSTR(string, start, length)

• string: The string from which to extract the substring.
• start: The position (index) at which to start extracting characters (1-based).
• length: Optional. The number of characters to extract. If omitted, the substring extends to the end of the string.

Example:

SELECT MID('Hello World', 7, 5);  -- Output: 'World'
SELECT SUBSTR('Hello World', 1, 5);  -- Output: 'Hello'

These are some of the common methods and statements concerning substring manipulation in MySQL. They provide flexibility in extracting substrings and working with string data in your SQL queries.