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: t-sql
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.
What is T-SQL?
Transact-SQL, often abbreviated to T-SQL or even TSQL, is Microsoft’s and Sybase’s proprietary extension to SQL. Transact-SQL expands on the SQL standard to include extra features that aren’t included in the SQL standard.
Here’s an example of a simple T-SQL statement:
CREATE DATABASE Movies;
This is as simple as a T-SQL example could get. This creates a new database called Movies.
However, T-SQL provides for other options to be included in this statement, such as where the database files should be located, the size of those files, what their maximum size should be, and more.
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 List Item at a Specified Position in SQL Server
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.
Example
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
How to Format Numbers in SQL Server
Starting from SQL Server 2012, you can format numeric types using the T-SQL FORMAT() function. This function accepts three arguments; the number, the format, and an optional “culture” argument.
It returns a formatted string of type nvarchar.
The format is supplied as a format string. A format string defines how the output should be formatted.
Here’s an example:
SELECT FORMAT(1, 'N');
Result:
1.00
In this case, I used N as the second argument. This is the standard numeric format specifier for Number. This particular format specifier (N) results in the output being formatted with integral and decimal digits, group separators, and a decimal separator with optional negative sign. This argument is case-insensitive, so either N or n is fine.
How to Format the Date & Time in SQL Server
In SQL Server, you can use the T-SQL FORMAT() function to format the date and/or time. Simply provide two arguments; the date/time and the format to use.
The format is supplied as a format string. A format string defines how the output should be formatted.
The FORMAT() function also accepts an optional “culture” argument, which allows you to specify a language/locale that the results should adhere to.
How to Add a Separator to a Concatenated String in SQL Server – CONCAT_WS()
In SQL Server and Azure, if you need to concatenate two or more strings, you can use the T-SQL CONCAT() function. As with any basic concatenation operation, this function joins the strings together, end-to-end.
But what if you need to add a separator between each string?
For example, you might want to make a comma-separated list of strings. In this case, you’d want to insert a comma in between each string. Like this:
Paris, France
Instead of this:
ParisFrance
Fortunately, T-SQL provides the CONCAT_WS() function that helps you do exactly that. The CONCAT_WS() function works just like the CONCAT() function, except that it takes an extra argument – the separator you’d like to use.
How to Concatenate Strings in SQL Server with CONCAT()
In SQL Server, you can concatenate two or more strings by using the T-SQL CONCAT() function. You can also use SQL Server’s string concatenation operator (+) to do the same thing. Both are explained here.
In SQL Server (and in any computer programming environment), string concatenation is the operation of joining character strings end-to-end.
Here’s an example:
SELECT CONCAT('Peter', ' ', 'Griffin') AS 'Full Name';
Result:
Full Name ------------- Peter Griffin
Note that I actually concatenated 3 strings here. I concatenated the first name, the last name, plus a space.