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.
Author: Ian
What is DATALENGTH() in SQL Server?
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
LEN() vs DATALENGTH() in SQL Server
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()- Returns the number of characters of the specified string expression, excluding trailing blanks.
DATALENGTH()- Returns the number of bytes used to represent any expression.
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
How to Install SQL Server on a Mac with VirtualBox
Here I’ll show you how to install SQL Server on a Mac with VirtualBox running Windows (a free trial edition).
The result of this is that you’ll have both Windows and SQL Server running on your Mac. And because you’re using VirtualBox, switching between macOS and Windows is as easy as switching between any other application.
As mentioned, this method involves Windows. If that scares you, then you might be better off installing SQL Server on your Mac via a Docker container. That method doesn’t involve Windows in any shape or form.
But if you don’t mind using Windows (or if you need to), here are the main steps for installing SQL Server for Windows on your Mac using VirtualBox:
- Download and Install VirtualBox
- Create a New Virtual Machine (VM)
- Download the Windows ISO image
- Install Windows
- Download and Install SQL Server
Below are more detailed instructions for each of these steps.
Continue reading
How to Set the Character Set and Collation of a Database in MySQL
In MySQL, you can specify the character set and collation at various levels. You can specify them at the connection level, the server level, the database level, the table level, and the column level. You can also specify a collation in your queries so that it overrides any collation that has been previously specified at the aforementioned levels.
To set the character set and collation at the database level, you can use the CREATE DATABASE statement or ALTER DATABASE statement (depending on whether you’re creating the database or modifying it).
Continue reading
How to Set the Character Set and Collation of a Table in MySQL
In MySQL, you can specify the character set and collation at various levels. You can specify them at the connection level, the server level, the database level, the table level, and the column level. You can also specify a collation in your queries so that it overrides any collation that has been previously specified at the aforementioned levels.
To set the character set and collation at the table level, you can use the CREATE TABLE statement or ALTER TABLE statement (depending on whether you’re creating the table or modifying it).
Continue reading
How to Set the Character Set and Collation of a Column in MySQL
In MySQL, you can specify the character set and collation at various levels. You can specify them at the connection level, the server level, the database level, the table level, and the column level. You can also specify a collation in your queries so that it overrides any collation that has been previously specified at the aforementioned levels.
To set the character set and collation at the column level, you can use the CREATE TABLE statement or ALTER TABLE statement (depending on whether you’re creating the table or modifying it), and specify the character set and collation within the column’s definition (the column that you want to set the character set/collation on).
Continue reading
How to Set the Collation of a Database in SQL Server (T-SQL)
When using SQL Server, you can use T-SQL to specify the default collation of a database. Specifically, you can use the COLLATE clause of either the CREATE DATABASE or ALTER DATABASE statements.
The COLLATE clause is optional – if you don’t use it when creating the database, the database will use the default collation of the server (which was specified at the time SQL Server was installed). And if you don’t specify the clause when altering the database, its default collation won’t change.
Continue reading
How to Set the Collation of a Column in SQL Server (T-SQL)
By default, each column in a SQL Server database uses the collation that’s been specified at the database level. And by default, the database collation is taken from the server collation. However, these collation settings can be overridden, both at the database level and the column level by explicitly setting the collation at that level.
This page demonstrates how to specify the collation of a column. You can set the collation by adding the T-SQLÂ COLLATE clause to the CREATE TABLE and ALTER TABLE statements. When you use those statements, you define the column and its properties, including any collation settings. The COLLATE clause is optional so if you don’t include it, the column will simply use the default collation of the database.
Continue reading
How to Show the Server Collation in SQL Server (T-SQL)
The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server, as well as any newly created user databases. The server collation is specified during SQL Server installation.
Below are two ways to check the server collation in SQL Server using Transact-SQL.
The SERVERPROPERTY() Function
One option is to use the SERVERPROPERTY() function:
SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server Collation';
This returns the collation like this:
Server Collation ---------------------------- SQL_Latin1_General_CP1_CI_AS