MySQL String Functions

Syntax:-

Here is the syntax of the MySQL ASCII() function:

ASCII(Input String)

Parameter Values

Technical Details

Examples:

Example 1: This example to return the ASCII value of (Capital) B:

SELECT ASCII('B');

Result:

Here, the input string is B, It returned the ASCII value of B: 66.

Example 2: This example to return the ASCII value of (Small) b:

SELECT ASCII('b');

Result:

Here, the input string is b, It returned the ASCII value of b: 98.

Example 3: In this example, we can use numeric values also with or without single quotes. it'll get you the left side first number's ASCII value.

SELECT ASCII('5');

Result:-

Otherwise, You can write the query like below,

SELECT ASCII(5);

Result:-

From the results you can see that the both return ASCII value of '5' as '53'.

Example 4: When more than one character is entered, the first character's value is will shown:

SELECT ASCII('jp');

Result:-

Here, although the argument is jp, but only returned the ASCII value of j: 106.

Example 5: This example will take the Name column and Age in the STUDENT table and give the ASCII value of the left first character:

Let's take this STUDENT table for the below Example:-

The query Will be like this,

SELECT 
ASCII(StudentName) AS AsciiCodeOfFirstChar,
ASCII(StudentAge) AS AsciiCodeOfFirstNumber,
FROM STUDENT;

Result:-

From the result, The values of the Name column are Sakunthala and Chandra, As per ASCII function, it takes the left side first-string 'S' ASCII value '83' and 'C' ASCII value '67' will be the output, same as the age column is 17 and 9, and it takes left side first number '1' ASCII value '49' and '9' ASCII value '57' will be the output.

The method CHAR LENGTH() returns the length of a string (in characters).

The function simply counts the number characters and ignore whether the character(s) are single-byte or multi-byte. Therefore a string containing three 2-byte characters, LENGTH() function will return 6, whereas CHAR_LENGTH() function will returns 3.

Note: This function is same as CHARACTER_LENGTH() function.

Remember: This function counts the white spaces as a character.

Syntax:-

Here is the syntax of the MySQL CHAR_LENGTH() function:

CHAR_LENGTH(Input String)

Parameter Values

Technical Details

Examples:-

Example 1: The following MySQL query will count the length of the specified string which is inside brackets:

SELECT CHAR_LENGTH("Simmanchith Tutorial") AS StringLength;

Result:-

From the above result, the specified String "Simmanchith Tutorial" length is 20. It also counts white spaces between the Input String.

Example 2: This example returns the length of the text in the "Name" and "Continent" columns should be returned:

Let's take this COUNTRY table for the below Example:-

The query is,

SELECT 
CHAR_LENGTH(CountryName) AS LengthOfName, 
CHAR_LENGTH(Continent) AS LengthOfContinent
FROM Customers;

Result:-

As you see in the result CountryName column returns LengthOfName "Saint Vincent and the Grenadines" is 14 and "North America" is 10, and the Continent column returns LengthOfContinent "Georgia" is 7 and "asia" is 4.

The method CHARACTER_LENGTH() returns the length of a string (in characters).

Note: CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().

Remember: This function counts the white spaces as a character.

Syntax:

Here is the syntax of the MySQL CHARACTER_LENGTH() function:

CHARACTER_LENGTH(string)

Parameter Values

Technical Details

Examples:-

Example 1: From this example length of the text in the columns should be returned:

Let's take this CUSTOMER table for the below Example:-

The query will be like this,

SELECT 
CHARACTER_LENGTH(CustomerName) AS LengthOfName, 
CHARACTER_LENGTH(CustomerEmail) AS LengthOfEmail 
FROM Customer;

Result:-

Through the result CustomerName column returns LengthOfName "Sakunthala" is 10, "Bala Murugan" is 12, and the CustomerEmail column returns LengthOfEmail "Sakunthala3815@yahoo.com" is 24 and "Bala-Murugan9570@gmail.com" is 26.

Example 2: The example gives the length of the specified input string:

SELECT CHARACTER_LENGTH('Simmanchith Tutorial') AS LengthOfString;

Result:-

The result will give the exact count of the input string 'Simmanchith Tutorial' the output will be 20 including spaces.

Main Article :- MySql CONCAT() Function

CONCAT() is a function that Concatenates two or more strings and returns a string..

The output is a nonbinary string if all arguments are nonbinary. The comparable nonbinary string representation of a numeric argument is created.

A binary string is returned if any of the parameters are binary strings.

Note: If any of the arguments are NULL, CONCAT() returns NULL.

Syntax:-

Here is the syntax of MySQL CONCAT() function:

CONCAT(expr1, expr2, expr3,...)

Parameter Values

Technical Details

Examples:-

Example 1: The example Combines the specific string inside the Concat function.

SELECT CONCAT("Simmanchith ", "Tutorial ", "is ", "Useful") AS ConcatenatedString;

Result:-

From the result input strings ("Simmanchith ", "Tutorial ", "is ", "Useful") inside concat function merge into (simmanchith tutorial is useful) single string.

Example 2: This example using Null value in Concat function:

SELECT CONCAT('Simman', NULL, 'chith') AS NullValue;

Result:-

You can understand from this output if one NULL value is included in the Concat function the result will be always NULL in MySQL.

Example 3: This Example Combining decimal numbers and numbers through the Concat function:

SELECT CONCAT(14.3, 23, 90);

Result:-

The result can prove that you can concatenate specific numbers, the numbers are also considered as a string by the Concat function.

Example 4: This example performs combining even without the Concat function, It can combine the specific expressions which is inside a quote:

SELECT 'I' 'Love' 'India';

Result:-

The result For the quoted strings, concatenation can be performed by placing the strings next to each other. So the result shows as ILoveIndia.

Example 5: This example Combine the following two columns into a single "CombinedColumns" column:

Let's take this BOOKSIMPLE table for the below Example:-

The query statement will be,

SELECT 
CONCAT(BookName, " ", BookPrice) AS CombinedColumns 
FROM BOOKSIMPLE;

Result:-

From the above query, the Concat function will merge a number of column values as a string, here BookName, BookPrice Columns are combined into a single String.

CONCAT WS() is a special form of CONCAT() that joins two or more expressions with a separator.

The first argument serves as a divider between the others. Between the strings to be concatenated, the separator is placed.

As with the rest of the inputs, the separator can be a string. The result will be NULL if the separator is NULL.

Note: The function CONCAT WS() doesn't avoid over empty strings. It does, however, omit any NULL values following the separator argument.

Syntax:-

Here is the syntax of MySQL CONCAT_WS() function:

CONCAT_WS(divider, string1, string2, ..., stringN)

Parameter Values

Technical Details

Examples:-

Example 1: Let's use this example to Combine numerous phrases and divide them with a "-" separator:

SELECT CONCAT_WS("-", "Simmanchith", "Tutorial", "is", "Useful") AS MergedString;

Result:-

This result is a "-" divider, which is added between every string.

Example 2: The example of CONCAT_WS() function ignores the NULL value parameter. See the example below:

SELECT CONCAT_WS('-', 'Welcome', NULL, 'Learner');

Results:-

Through the result, you can see that separator is inserted between each string value.

Example 3: Use this example to Integrate the five columns listed below into one "Details" column (with a space between them):

Let's take this EMPLOYEEADVANCE table for the below Example:-

The query for requirement,

SELECT 
CONCAT_WS(" | ", Name, Gender, City, Dept, Salary) AS Details
FROM EMPLOYEEADVANCE;

Result:-

From the result, concat_ws function is used to merge the Name, Gender, City, Dept, and Salary columns using " | " this separator.

In MySQL, the FIELD() function returns the index (position) location of a string in a list of strings.

This function searches with no regard for case.

Note: If the specified value sometimes doesn't exist in the list of values, this function returns 0. If the value is NULL, this function returns 0, because NULL fails equality comparison with any value.

FIELD() compares all parameters as strings if they are all strings. When all of the reasons are numerical, they are evaluated numerically. The arguments are otherwise evaluated by comparing as double.

Note: FIELD() is the complement of ELT().

Syntax:-

Here is the syntax of MySQL FIELD() function:

FIELD(string, str1, str2, ..., strN)

Parameter Values

Technical Details

Examples:-

Example 1: This example Return the index for the position of "j" in the string list:

SELECT FIELD("j", "a", "n", "j");

Result:-

From the result, the searched string j was compared to the remaining list of strings, The string j was found in 3rd position.

Example 2: In this example, let's see if there is no matching available in the list:

SELECT FIELD("c", "a", "b");

Result:-

As per the result, you can see that if there is no matching string available in the list the result will be 0.

Example 3: In the before examples, the small letter compares to the small letter, here we use Capital letters to compare small letters:

SELECT FIELD("A", "v", "a", "p");

Result:-

This result shows that the searching string "A", compares to the remaining string in the lists. after the comparison small letter "a" is found in the 2nd position.

Example 4: In previous examples string values were used for comparison, this example used numeric values, to return the 9th index position:

SELECT FIELD(9, 5, 7, 0, 9, 4, 3);

Result:-

From the result, Number 9, is found at 4th place.

Example 5: In this case, the FIELD() function returns 0 if the value is NULL:

SELECT FIELD(NULL, 'J', 'P', NULL);

Result:-

if we mention null as a matching string 0 will be the output.

Main Article :- MySql FORMAT() Function

The FORMAT() function Format the number to a format like '#,###,###.##' a number to a string format, then round it to a specified number of decimal places, and it returns the result as a string.

Syntax:

Here is the syntax of MySQL FORMAT() function:

FORMAT(numeric value, decimal_places, locale)

Parameter Values

Technical Details

Examples:-

Example 1: The number should be formatted as "#,###,###.##" in this case (and rounded to two decimal places):

SELECT FORMAT(459582.6789, 3);

Result:

As per the result, the decimal point 459582.6789 was rounded to 459,582.689 based on the specified numeric value.

Example 2: This example Format the number in the following format: "#,###,###.##" (rounding to 0 decimal places):

SELECT FORMAT(250500.5634, 0);

Result:

Through the result, you can see that we didn't pass numeric values so the actual decimal rounding will be performed.

Example 3: The following statement uses the de_DE locale instead of the en_US locale:

SELECT FORMAT(4532.3,4,'de_DE');

Result:

The de_DE locale uses a dot (.) for thousand grouping and a comma (,) for decimal mark, as shown in the output.

The INSERT() method in MySQL replaces a substring with a new string based on its start position and specified amount of characters.

Returns NULL if any argument is NULL.

Note: This function is multibyte safe.

Syntax

Here is the syntax of MySQL INSERT() function:

INSERT(str, state, num, str2)

Parameter Values

Return Values

• If state is longer than the length of the string, the original string is returned.
• If number is greater than the length of the rest of the string, this function substitutes string from position to the end.

Technical Details

Examples:-

Example 1: From this example the string "simmanchith.com", add the term "Learners". The first eleven characters must be replaced :

SELECT INSERT(simmanchith.com", 1,11, "Learners");

Result:

As per the result, the website simmanchith contains 11 characters so the position range is selected from 1 to 11, and the "simmanchith" string is replaced by "Learners".

Example 2: We replaced the first 11 characters in the previous example, now let's replace the last three characters:

SELECT INSERT("simmanchith.com", 11, 3, "in");

Result:

The simmanchith.com string as per mentioned position 11, 3 the last 3 characters "com" will replace into new string "in".

Example 3: If any of the arguments are NULL, the method returns NULL:

SELECT
    INSERT(NULL, 7, 1, ' ') NullOne,
    INSERT('Simman_chith', NULL, 1, ' ') NullTwo,
    INSERT('Simman_chith', 7, NULL, ' ') nullThree,
    INSERT('Simman_chith', 7, 1, NULL) NullFour;

Result:-

As per result if there is a null value in the function the output will be null.

The INSTR() function returns the location of the first occurrence of a string within another string.

This function is case-sensitive only if at least one argument is a binary string.

With two parameters, the INSTR() method is identical to the POSITION() and LOCATE() functions.

The LCASE() function in MySQL transforms and returns the provided string in lowercase.

Note:The LCASE() function and the LOWER() function are interchangeable.

Syntax

Here is the syntax of MySQL LCASE() function:

LCASE(str)

Parameter Values

Technical Details

Examples:-

Example 1: As an example, Convert the case of the string to lowercase:

SELECT LCASE("Simmanchith Tutorial is USEFUL");

Result:

Using this string "Simmanchith Tutorial is USEFUL" inside the function is converted to lowercase.

Example 2: When we use Null value in this example:

SELECT LCASE(NULL);

Result:

When the str parameter value is NULL, the function returns NULL.

Example 3: In this example text in "BookName" column transform into lowercase:

let's take this BOOKADVANCE to execute the function:

The query will be,

SELECT BookName,
LCASE(BookName) AS LowercaseBookName
FROM BOOKADVANCE;

Result:

From the result, BookName columns all data will be changed into lowercase.

Main Article :- MySql LEFT() Function

The LEFT() method in MySQL returns the string leftmost N characters.

Note: This function starting from left.

Syntax

Here is the syntax of MySQL LEFT() function:

LEFT(str, set_of_charaters)

Parameter Values

Technical Details

Examples:-

Example 1: This example Extract the following three characters from a string :

SELECT LEFT("Simmanchith Tutorial", 6) AS StringExtraction;

Result:

From the result, this function starts at the left and extracts the first 6 leftmost characters.

Example 2: As an illustration, consider the following: The function take value as an empty string if length is zero, negative, or null :

SELECT
LEFT('Welcome', 0),
LEFT('Welcome', -1),
LEFT('Welcome', NULL),
LEFT(NULL, NULL);

Result:

Through the result, If the function take NULL as input, the output will be NULL, and if the function takes a negative or zero as input, the output will be empty.

Example 3: This illustration Extract the following 5 characters from the text of the "CustomerName" column (starting at the left):

let's take this CUSTOMER to execute the function:

The query will be,

SELECT 
LEFT(Name, 6) AS StringNameExtraction,
LEFT(Email, 10) AS StringEmailExtraction 
FROM CUSTOMER;

Result:

From the result, it takes two columns from the customer table: the CustomerName column extracts the first 6 leftstring, and the Email column extracts the first 10 leftstring.

Main Article :- MySql LENGTH() Function

The LENGTH() function calculates the length of a string.

Multibyte character counts as multiple bytes, and the function is measured in bytes. Which means that LENGTH() returns 10 for a string with five 2-byte characters, whereas CHAR_LENGTH() returns 5.

Note: In MySQL, the Length() OpenGIS geographic function is called ST_Length().

Remember: The length function calculate space as a character.

Syntax

Here is the syntax of MySQL LENGTH() function:

LENGTH(str)

Parameter Values

Technical Details

Examples:

Example 1: This example calculates length of the specified String:

SELECT LENGTH("Simmanchith Tutorial") AS TotalLengthOfString;

Result:

The length of the input string "Simmanchith Tutorial" is 20 as a result of the calculation.

Example 2: Let's check different characters, positive or negative numbers, null, now, curdate as a string input inside the length function:

SELECT
LENGTH('') AS EmptyLength,
LENGTH(20) AS NumberLength, 
LENGTH(-20) AS NegativeNumberLength, 
LENGTH(+20) AS PositiveNumberLength,
LENGTH(NOW()) AS NowLength,
LENGTH(CURDATE()) AS CurrentDateLength, 
LENGTH(NULL) AS NullLength;

Result:

Explaination:

• LENGTH('') gives you 0, because it's empty. if you put space inside code it will return length based on spaces.
• LENGTH('20') returns 2. Because +20 equals 20, LENGTH(+20) gives us 2.
• LENGTH(-20) gives you 3. This is due to the fact that the matching string for LENGTH(-20) is '-20', and LENGTH('-20') yields 3.
• LENGTH('2022-05-10 08:12:57') is the same as LENGTH(NOW()).
• LENGTH(NULL) gives You null value, because it have no characters.

Example 3: This example return the number of bytes in the "StudentName" column's text:

Let's take "STUDENT" table to execute the query:

The query will be look like,

SELECT 
StudentName, LENGTH(StudentName) AS LengthOfStudentName
FROM STUDENT;

Result:

We got the StudentName column from the result and used the length function to calculate the length of the name in the StudentName column.

Main Article :- MySql LOCATE() Function | MySql INSTR() Function | MySql POSITION() Function

The LOCATE() method finds the first instance of a substring in a string.

If the substring cannot be found within the original string, this function returns 0.

The LOCATE() function are case-insensitive. The function is the same as POSITION() and INSTR() with two parameters.

Syntax:

Here is the syntax of MySQL LOCATE() function:

LOCATE(substr, str)

or

LOCATE(substring, string, startPos)

Parameter Values

Technical Details

Examples:-

Example 1:Return position: Search for "a" in the text "simmanchith.com."

SELECT LOCATE("a", "simmanchith.com") AS MatchPosition;

Result:

This query searching for 'a' in simmanchith.com, the a is found at 5th position.

Example 2: Return position: search for "com" in the text "simmanchith.com" (starting at position 3).

SELECT LOCATE("com", "simmanchith.com", 3) AS MatchPosition;

Result:

The result search string "com" located in the string "simmanchith.com" finds the starting at position 3.

Example 3: This example Find "e" in the BookName column and return the following position:

Let's use BOOKSIMPLE table to execute the example:

The query will be look like this,

SELECT 
LOCATE("e", BookName) AS LocationFoundAt
FROM BOOKSIMPLE;

Result:

From the BookSimple table, search for the "e" string in the BookName column.

Main Article :- MySql LCASE() Function | MySql LOWER() Function

The LOWER() method in MySQL returns a lowercase version of the provided string.

The function will NULL return when the string parameter is NULL.

Note: LCASE() and LOWER() are the same thing.

Syntax

Here is the syntax of MySQL LOWER() function:

LOWER(String)

Parameter Values

Technical Details

Examples:-

Example 1: The illustration Change the case of the text to lowercase:

SELECT LOWER("Simmanchith Tutorial is USEFUL") AS LowerText;

Result:

The input string "Simmanchith Tutorial is Useful" gets transformed to lowercase as a result of this.

Example 2: Using NULL value as input string in this example:

SELECT LOWER(NULl);

Result:

When we use the NULL value in lower function it will return NULL.

Example 3: Reduce the capitalization of the text in "CountryName" with this example:

Let's take COUNTRY table to execute the example:

The query will be like this,

SELECT 
LOWER(Code) AS LowercaseCountryCode,
LOWER(CountryName) AS LowercaseCountryName
FROM Country;

Result:

The Lower function is used in this result to transform the columns Code and CountryName to lowercase.

The LPAD() method in MySQL left pads a string with another string of the given length.

Note: If you want to right pad a string, use the RPAD() function.

Syntax

Here is the syntax of MySQL LPAD() function:

LPAD(str, len, padstr)

Parameter Values

Technical Details

Examples:-

Example 1: Add "HI" to the left side of the string to make it a total length of 10:

SELECT LPAD("Hello", 10, "HI");

Result:

The length of input string "Hello" is calculated as 5. The length of input length is 10. The padding string "HI" will be added on the left side to satisfy the overall length of 10.

Example 2: Let's use negative number, and null in the lpad function in this example:

SELECT 
LPAD('Hello', -1, 'D') AS LpadNegative,
LPAD('NULL', -1, 'U') AS LpadNull;

Result:

The output will be null if the length is negative. The output will be null if a string is given as null.

Example 3: For instance, to make "EmployeeName" 30 characters long, pad the text with "IND":

Let's take "EMPLOYEESIMPLE" table to execute example:

SELECT 
LPAD(EmployeeName, 30, "IND") AS LeftPadEmployeeName
FROM EMPLOYEESIMPLE;

Result:

From this result, length of input column EmployeeName will be calculated. The length of input length is 20. The padding string "IND" will be added on the left side to satisfy the overall length of 20.

Using the LTRIM() method it will returns the string with leading space characters removed.

Note: The function will return NULL, if the parameter is NULL.

Syntax

Here are some examples of MySQL LTRIM() function.

LTRIM(text)

Parameter Values

Technical Details

Examples:-

Example : To eliminate leading spaces from a string, follow the steps below:

SELECT LTRIM("     Simmanchith Tutorial") AS TrimmedLeftString;

Result:

From the result, the string has some extra space before the string " Simmanchith Tutorial". After using this function, the extra spaces will be removed.

The MID() function in MySQL returns a substring of a given length beginning at the provided location.

Note: The SUBSTRING() routine is identical to the MID() and SUBSTR() routines.

Syntax

Here is the syntax of MySQL MID() function:

MID(str, begin, len)

Parameter Values

Technical Details

Examples:-

Example 1: To remove a substring from a string, use this example (starting at position 7, remove five characters):

SELECT MID("Simmanchith Tutorial", 7, 5) AS ExtractedString;

Result:

The input string "Simmanchith Tutorial" is used to Extract the result. The string starts at the 7th position, and you specify 5 as a character length and then "chith" will be the result.

Example 2: To remove a substring from a string, use this example (starting at position -8, remove five characters):

SELECT MID("Simmanchith Tutorial", -8, 5) AS ExtractStringReverse;

Result:

The input string "Simmanchith Tutorial" is used to extract the result. Here the beginning position is a negative number -8, so the search will begin right to left, because of the specified 5 character length, and then it will return "Tutor".

Example 3: Use this example to remove substring from a EmployeeName column:

Let's take EMPLOYEEADVANCE table to execute the example:

The query will be look like this,

SELECT MID(EmployeeName, 2, 5) AS ExtractEmployeeNameString
FROM EMPLOYEEADVANCE;

Result:

From the result, the string was extracted from the Employeename column, Starting at position 2, and extracted 5 characters from the text in a column to get an extractemployeename column as output.

The Location() method returns the first appearance of a substring in a string.

If the substring cannot be located within the original string, this function returns 0.

This function doesn't consider case when searching.

Note:The POSITION() and LOCATE() functions are interchangeable.

Syntax

Here is the syntax of MySQL POSITION() function:

POSITION(substr, str)

Parameter Values

Technical Details

Examples:-

Example 1: The Return position: Search for "a" in the string "simmanchith.com."

SELECT POSITION("a" IN "simmanchith.com") AS MatchPosition;

Result:

From the result the matching string "a" matched at 5th position.

Example 2: Return position: search for "COM" in string "simmanchith.com"

SELECT POSITION("COM" IN "simmanchith.com") AS MatchPosition;

Result:

Through the result, the whole string "COM" matched at the 11th position in the input string "simmanchith.com".

Example 3: Extracts "e" value from this example:

Let's use BOOKSIMPLE table to execute the example:

The query will be look like this,

SELECT 
LOCATE("e" IN BookName) AS LocationFoundAt
FROM BOOKSIMPLE;

Result:

From the BookSimple table, search for the "e" string in the BookName column.

The REPEAT() method repeats a string for the provided number of times.

Syntax

REPEAT(string, number)

Parameter Values

Technical Details

Example:

Repeat a string 3 times:

SELECT REPEAT("SQL Tutorial", 3);

Result

More Examples:

Repeat the text in CustomerName 2 times:

SELECT REPEAT(CustomerName, 2)
FROM Customers;

Result

Repeat the string 0 times:

SELECT REPEAT("SQL Tutorial", 0);

Result

Main Article :- MySql REPLACE() Function

The REPLACE() method creates a new substring for every instances of a substring within a string.

Note:This method replaces characters in a case-sensitive manner.

Main Article :- MySql REVERSE() Function

The REVERSE() method returns a string that has been reversed.

Main Article :- MySql RIGHT() Function

The RIGHT() method takes a string and extracts a set of characters (starting from right).

Tip:Examine the LEFT() method as well.

The RPAD() function right-pads a string with another string until it reaches a specified length.

Note: Examine the LPAD() method as well.

The RTRIM() method eliminates spaces from the end of a string.

The SPACE() method returns a string with the amount of space characters you choose.

Two strings are compared using the STRCMP() function.

A substring is extracted from a string using the SUBSTR() method (starting at any position).

Note: The methods SUBSTR() and MID() are the same as SUBSTRING().

Main Article :- MySql SUBSTRING() Function

A substring is extracted from a string using the SUBSTRING() method (starting at any position).

Note: The SUBSTR() and MID() functions are interchangeable with the SUBSTRING() function.

Main Article :- MySql TRIM() Function

Leading and trailing spaces are removed from a string using the TRIM() method.

Main Article :- MySql UCASE() Function | MySql UPPER() Function

The UCASE() method changes the case of a string.

Note: The UPPER() function is equivalent to this one.

The UPPER() method raises the case of a string.

Note: The UCASE() method is the same as this one.