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.
Tag: functions
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 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
How to Show the Collation of a Database in SQL Server (T-SQL)
Here are two ways to return the collation of a database in SQL Server using Transact-SQL.
Query sys.databases
The first option is to run a query against sys.databases to return the collation of a specific database. The WHERE clause allows you to narrow the results down to the database/s you’re interested in:
SELECT
name,
collation_name
FROM sys.databases
WHERE name = 'Music';
This results in something like this:
name collation_name ----- ---------------------------- Music SQL_Latin1_General_CP1_CI_AS
In this case, we specified the database called Music.
Continue reading
How to Find the Collation in SQL Server (T-SQL)
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.
Server Level Collation
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
How to Check your SQL Server Version
SQL Server, or more specifically, Transact-SQL, includes a number of built in functions, including the @@version scalar function. The @@version function is a configuration function, which returns system and build information for the current installation of SQL Server. You can run this function at any time to find out which version of SQL Server you’re using.
Here’s how:
SELECT @@version;
When running that in a command line interface, you might see results that look like this:
Microsoft SQL Server 2017 (RTM-CU6) (KB4101464) - 14.0.3025.34 (X64) Apr 9 2018 18:00:41 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 16.04.4 LTS) 1 row(s) returned Executed in 1 ms
How to Check your MySQL Version
MySQL includes the @@version system variable and the VERSION() function that enable you to find out what version of MySQL you’re running.
The VERSION() Function
Here’s an example of using the VERSION() function:
SELECT VERSION();
This simply returns the version number and no more. Like this:
8.0.11
How to Find the Database Collations Supported by your SQL Server Instance
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.
Full List of Collations Supported in SQL Server 2017
Below is a table of all collations supported in SQL Server 2017 (warning: It’s a long list!).
This list was returned by running the following code:
SELECT name, description FROM sys.fn_helpcollations();
That returns the following list of 3955 collations.
Continue reading