In SQL Server, you can use T-SQL to check the compatibility level of a database. All you need to do is query sys.databases to find the compatibility level for the database in question.
Here’s an example:
SELECT compatibility_level
FROM sys.databases
WHERE name = 'WideWorldImporters';Result:
compatibility_level ------------------- 130
This example returns the compatibility level of the WideWorldImporters database.
You can use the MySQL SUBSTRING_INDEX() function to return everything before or after a certain character (or characters) in a string.
This function allows you to specify the delimiter to use, and you can specify which one (in the event that there’s more than one in the string).
Here’s the syntax:
SUBSTRING_INDEX(str,delim,count)
Where str is the string, delim is the delimiter (from which you want a substring to the left or right of), and count specifies which delimiter (in the event there are multiple occurrences of the delimiter in the string).
Note that the delimiter can be a single character or multiple characters.
In MySQL, the SUBSTRING() function enables you to return a substring from a string. So you can use this function to grab a portion of text from a larger piece of text.
There are several different ways to use the SUBSTRING() function, and this affects the syntax.
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.
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_expression is the original string. This can actually be a constant, variable, or column of either character or binary data.start specifies the start position (i.e. where the new string will be inserted).length is how many characters are to be deleted from the original string.replaceWith_expression is the string that’s being inserted. replaceWith_expression can be a constant, variable, or column of either character or binary data.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.
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 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.
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.
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
Starting from SQL Server 2012, you can use the T-SQL CHOOSE() function to find a list item at a specified index position within a list.
The syntax goes like this:
CHOOSE ( index, val_1, val_2 [, val_n ] )
Where index is an integer that represents the position within the list that you want to return.
Here’s an example:
SELECT CHOOSE(3, 'Marge', 'Homer', 'Bart') AS 'Who is at 3?';
Result:
Who is at 3? ------------ Bart
In this case, we want to find the item at position 3. The item at position 3 is Bart.
Continue reading