Tag: mssql

How to Find a List Item at a Specified Position in SQL Server

Posted on by Ian

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

Posted on by Ian

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.

Continue reading →

How to Format the Date & Time in SQL Server

Posted on by Ian

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.

Continue reading →

How to Add a Separator to a Concatenated String in SQL Server – CONCAT_WS()

Posted on by Ian

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.

Continue reading →

How to Concatenate Strings in SQL Server with CONCAT()

Posted on by Ian

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.

Continue reading →

What’s the DATALENGTH() Equivalent in MySQL?

Posted on by Ian

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 →

LEN() vs DATALENGTH() in SQL Server

Posted on by Ian

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 Specify the Collation in a Query in SQL Server (T-SQL)

Posted on by Ian

In database systems, collation determines how data is sorted and compared in a database. For example, when you run a query using the ORDER BY clause, collation determines whether or not uppercase letters and lowercase letters are treated the same.

In SQL Server, collation is specified at the server level, the database level, and the column level.

Collation can also be applied to a character string expression to apply a collation cast. For example, you can use the COLLATE clause in a T-SQL SELECT statement to specify the collation to be used. Like this:

USE Music;
SELECT ArtistId, ArtistName
FROM Artists
ORDER BY ArtistName COLLATE Latin1_General_CI_AI;

That collation uses CI for case-insensitive, and AI for accent-insensitive.
Continue reading →