Transact-SQL (T-SQL) includes a number of scalar functions that allow us to perform operation on strings when working with SQL Server. These functions accept an input string, and return either a string or a numeric value.
The following is a list of T-SQL string functions available in SQL Server. Click on each function or operator name to see an explanation of the function, its syntax, and examples.
Here, I explain how to use Azure Data Studio (formerly SQL Operations Studio) to create a database in SQL Server.
In my case, I’m running SQL Server on a Mac (via a Docker container), but these instructions are generic and should work exactly the same way on Linux and Windows.
This tutorial assumes you already have Azure Data Studio installed on your machine, and you’ve connected to your SQL Server instance. If you don’t have Azure Data Studio installed on your machine, here are instructions for installing it on a Mac and connecting to SQL Server via Docker. The Azure Data Studio installation is pretty straightforward (you install it just like any other software), so if you’re on Linux or Windows, you shouldn’t have any issues.
Restoring a database is a piece of cake with Azure Data Studio. It’s a similar process to doing it with SQL Server Management Studio. Simply click Restore and follow the prompts.
This restore process allows you to navigate through the computer’s file system to locate the .bak file. This .bak file contains a backup of the database you want to restore. So when SQL Server restores the database, it’s using the .bak file to do so.
However, if you’re running your SQL Server instance inside a Docker container (which of course, you would be if you’re running SQL Server on Mac or Linux), there’s something you need to be aware of if your backup file is located outside the Docker container.
Azure Data Studio (previously known as SQL Operations Studio) is a free tool that you can use to manage SQL Server. It uses a graphical user interface (GUI) that helps you view the various databases and objects within a SQL Server instance. It can run on Windows, macOS, and Linux, and it’s also designed to be used with Azure SQL Database, and Azure SQL Data Warehouse.
Here I explain how to install Azure Data Studio onto a Mac, then how to use it to connect to SQL Server.
Microsoft Azure Data Studio is a free, cross-platform tool that can be used to manage SQL Server, Azure SQL Database, and Azure SQL Data Warehouse.
Azure Data Studio was formerly called SQL Operations Studio (while it was in preview release), and it was renamed to Azure Data Studio once it was moved to general availability (GA) on September 24, 2018.
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
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:
Below are more detailed instructions for each of these steps.
Continue reading
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