In SQL Server, you can use the T-SQL SUBSTRING() function to return a substring from a given string.
You can use SUBSTRING() to return parts of a character, binary, text, or image expression.
Tag: string functions
What’s the MySQL Equivalent of STUFF() in SQL Server?
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.
How to Insert a String into another String in SQL Server using STUFF()
In SQL Server, you can use the T-SQL STUFF() function to insert a string into another string. This enables you to do things like insert a word at a specific position. It also allows you to replace a word at a specific position.
Here’s the official syntax:
STUFF ( character_expression , start , length , replaceWith_expression )
character_expressionis the original string. This can actually be a constant, variable, or column of either character or binary data.startspecifies the start position (i.e. where the new string will be inserted).lengthis how many characters are to be deleted from the original string.replaceWith_expressionis the string that’s being inserted.replaceWith_expressioncan be a constant, variable, or column of either character or binary data.
How to Insert a String into another String in MySQL using INSERT()
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.
The Difference Between FIELD() and FIND_IN_SET() in MySQL
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.
How to Return the Position of a List Item in MySQL
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.
CHARINDEX() vs PATINDEX() in SQL Server – What’s the Difference?
In SQL Server, you can use either the CHARINDEX() function or the PATINDEX() function to find a string within a string. These are Transact-SQL string functions, and they’re also available on Azure databases.
On the surface, these functions appear to do exactly the same thing, and in many cases, you could use whichever you prefer to use.
However, there a a couple of distinctions that could dictate which function you decide to use in certain scenarios. These can be summarized by the following:
PATINDEX()allows you to use wildcard characters to search for patterns.CHARINDEX()doesn’t.CHARINDEX()accepts a third argument which allows you to specify the start position of the search.PATINDEX()doesn’t.
More detail on these points below.
How to Find a String within a String in SQL Server
In SQL Server, you can use the T-SQL CHARINDEX() function or the PATINDEX() function to find a string within another string. Here’s a quick overview of each function.
The CHARINDEX() Function
This function accepts 3 arguments; the string to find, the string to search, and an optional start position.
The CHARINDEX() syntax goes like this:
CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
Where expressionToFind is the expression you want to find in the other string, and expressionToSearch is the other string. The optional start_location can be used to specify a position within expressionToSearch for which to start searching.
Note that only the position of the first occurrence is returned.
How to Return an Argument’s Position within a List of Arguments in MySQL
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.
How to Add a Separator to a Concatenated String in MySQL – CONCAT_WS()
In MySQL, the CONCAT_WS() function allows you to add a separator to concatenated strings. If you just use the CONCAT() function, you’d have no separator (unless you explicitly added a separator as an argument between each string argument).
A common usage of the CONCAT_WS() function is to create a comma-delimited list.