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.
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.
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.
If you’ve been working with SQL Server for any length of time, you might have encountered the Datalength() function. This function returns the number of bytes used to represent an expression.
But if you’ve moved to MySQL, you may be looking for a function that does the same thing.
In MySQL, the Length() function does basically the same thing that the T-SQL Datalength() function does in SQL Server (and Azure). MySQL’s Length() function returns the length of a string, measured in bytes.
Continue reading
In SQL Server (and Azure), the T-SQL LEN() function returns the number of characters of a specified string expression, excluding trailing blanks.
You provide the string as an argument.
In SQL Server (and Azure), the T-SQL DATALENGTH() function returns the number of bytes used to represent any expression.
For example:
SELECT DATALENGTH('Lit');
Result:
3
In this case, there are 3 bytes in the string Lit.
However, this is a simple example. The results can look a lot different, depending on the data type.
Continue reading
When using T-SQL in SQL Server (or Azure) the LEN() and DATALENGTH() functions will often return the same result, but not always. There are some cases where these functions will return completely different results for what appears to be the same data. This is because there’s an important difference between how the LEN() and DATALENGTH() functions work, as we’ll see here.
First up, here’s a quick definition of each:
LEN()DATALENGTH()Note “characters” vs “bytes”. Also note that “excluding trailing blanks” only applies to one.
Here are some examples to demonstrate the differences between LEN() and DATALENGTH().
Continue reading
Collation can be specified at the server level, database level, column level, expression level, and the identifier level. A different method is required for each of these.
Here’s how to find the server level collation:
SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server Collation';
This returns the collation like this:
Server Collation ---------------------------- SQL_Latin1_General_CP1_CI_AS
You can run the following query to return all the collations that are supported in your instance of SQL Server:
SELECT name, description FROM sys.fn_helpcollations();
Note that this returns quite a long list. Running that statement on SQL Server 2017 returns 3955 collations.
This is a quick article to demonstrate how to use Transact-SQL to change the collation of a database in SQL Server.
Here’s the basic code:
USE master; GO ALTER DATABASE Solutions COLLATE French_CI_AS ; GO
This first changes to the master database, then we use the ALTER DATABASE statement to change the collation of the Solutions database to French_CI_AS collation.
Continue reading