MySQL has a number of string functions that return the position of a substring within a string. More specifically, they return the position of the first occurrence within the string (or the first occurrence after a given starting point).
The functions I’m referring to are as follows:
INSTR()LOCATE()POSITION()Below is an overview of each one.
In MySQL, you can use the ASCII() function to return the ASCII code for a given character. More specifically, it returns the ASCII code for the leftmost character in a given string.
You can also use ORD(), which works the same way, except that it also supports multibyte characters.
In MySQL, you can use the LOWER() function to convert any uppercase characters to lowercase. Alternatively, you can use the LCASE() function, which is a synonym for LOWER().
The syntax goes like this:
LOWER(str)
Or…
LCASE(str)
Where str is the string you want converted to lowercase.
In MySQL, you can use the UPPER() function to convert any lowercase characters to uppercase. Alternatively, you can use the UCASE() function, which is a synonym for UPPER().
The syntax goes like this:
UPPER(str)
Or…
UCASE(str)
Where str is the string you want converted to uppercase.
If you ever need to reverse the order of a string value in MySQL – that is, return a string with the order of the characters reversed – the REVERSE() function is what you need.
If you’re familiar with SQL Server, you might know that you can use the T-SQL STUFF() function to insert a string within a string. As it turns out, MySQL has a similar function – but with a different name.
MySQL’s INSERT() function does essentially the same thing that the T-SQL STUFF() function does.
With the exception of a couple of minor differences (see below), both functions work exactly the same.
In MySQL, you can use the INSERT() function to insert a string into another string.
You can either replace parts of the string with another string (e.g. replace a word), or you can insert it while maintaining the original string (e.g. add a word). The function accepts 4 arguments which determine what the original string is, the position with which to insert the new string, the number of characters to delete from the original string, and the new string to insert.
Here’s the syntax:
INSERT(str,pos,len,newstr)
Where str is the original string, pos is the position that the new string will be inserted, len is the number of characters to delete from the original string, and newstr is the new string to insert.
MySQL includes a FIELD() function and a FIND_IN_SET() function that both return the position of a string within a list. However, these functions work slightly differently to each other.
The main difference between these two functions is this:
FIND_IN_SET() returns the index position of a string within a string list.FIELD() returns the index position of a string within a list of arguments.So one function searches a string list, and the other function searches a list of arguments.
In MySQL, you can use the FIND_IN_SET() function to return the index of a given list item within a string list (for example ‘item1, item2, item3,…’).
The function takes two arguments; the string to find, and the list to search.
The syntax goes like this:
FIND_IN_SET(str,strlist)
Where str is the the string you’re looking for, and strlist is the string list to search through.
In MySQL, you can use the ELT() function to return an item from a specified position in a list. SQL Server has a similar function, but with a different name.
In SQL Server, the CHOOSE() function does basically the same thing that the ELT() function does in MySQL. To be more precise, CHOOSE() is actually a Transact-SQL function, so it can also be used in Azure databases.