MySQL String Functions - Complete Tutorial

CONCAT() Function

The CONCAT() function is used to join two or more strings into a single string. It is very useful when you want to combine columns or add text to existing data.

• Syntax: CONCAT(string1, string2, ...)
• Returns NULL if any argument is NULL.

SUBSTRING() Function

SUBSTRING() extracts a portion of a string starting from a specified position for a given length. It helps when you need to isolate parts of a string.

• Syntax: SUBSTRING(string, start_position, length)
• If length is omitted, returns substring from start_position to end.

LENGTH() and CHAR_LENGTH() Functions

LENGTH() returns the length of a string in bytes, while CHAR_LENGTH() returns the number of characters. This distinction matters for multi-byte character sets.

• LENGTH('abc') returns 3
• CHAR_LENGTH('abc') returns 3
• For multibyte characters, LENGTH() may return a larger number.

REPLACE() Function

REPLACE() substitutes all occurrences of a substring within a string with another substring. It's useful for data cleaning and formatting.

• Syntax: REPLACE(string, from_substring, to_substring)
• Replaces all instances of from_substring with to_substring.

Concatenating First and Last Names

You can combine first and last name columns into a full name using CONCAT().

Extracting Area Code from Phone Number

Use SUBSTRING() to extract the area code from a phone number string.