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.
SQL Server has a CHOOSE() function that returns a list item at a given position. MySQL also has an equivalent function, but with a different name.
In MySQL, the ELT() function does essentially the same thing that CHOOSE() does in SQL Server. It allows you to find the list item at a given position within the list.
In MySQL, you can use the ELT() function to return a list item at a given position in the list.
The syntax goes like this:
ELT(N,str1,str2,str3,...)
Where N is the position of the item you want to return, and str1,str2,str3,... is the list.
Here’s an example:
SELECT ELT(3, 'Marge', 'Homer', 'Bart') AS 'Who is at 3?';
Result:
+--------------+ | Who is at 3? | +--------------+ | Bart | +--------------+
In this case we specify that we want to return the 3rd item in the list (because the first argument is 3) . And in this case the list is 'Marge', 'Homer', 'Bart', so the 3rd item is Bart. Continue reading
In MySQL, you can use the FIELD() function to return the position of a given string or number within a list of arguments. The function returns the index (position) of the first argument in the list of subsequent arguments.
The syntax goes like this:
FIELD(str,str1,str2,str3,...)
Where str is the item you want to find, and str1,str2,str3,... is the list you’re searching through.
Here’s an example:
SELECT FIELD('Homer', 'Marge', 'Homer', 'Bart') AS 'Where is Homer?';
Result:
+-----------------+ | Where is Homer? | +-----------------+ | 2 | +-----------------+
In this example, the list is: 'Marge', 'Homer', 'Bart' and we’re searching for the string Homer within that list. And because Homer is the 2nd item in the list of arguments, the result is 2.
In MySQL, the DATE_FORMAT() function allows you to format the date and time.
Here’s an example:
SELECT DATE_FORMAT('2018-12-01', '%W, %d %M %Y');
Result:
Saturday, 01 December 2018
In this example, %W is for the weekday name, %d is for the day of the month, %M is for Month, and %Y is for Year. There are many more format specifiers available that enable you to specify a precise format for dates, as well as the time component.
MySQL has a number of functions that allow you to format the date and time. These include functions such as DATE_FORMAT() and TIME_FORMAT().
When using these functions, you provide arguments that tell it what format you’d like the date or time to be in. These arguments are called format specifiers, and they begin with a percentage sign (%) followed by a character (usually a letter, but not always).
The following table lists the format specifiers that can be used when formatting the date and time in MySQL.