DBMS – Page 379

How to Format Numbers in SQL Server

October 25, 2021May 3, 2018 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.

How to Format the Date & Time in SQL Server

January 28, 2021May 2, 2018 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.

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

February 14, 2020May 2, 2018 by Ian

In MySQL, the CONCAT_WS() function allows you to add a separator to concatenated strings. If you just use the CONCAT() function, you’d have no separator (unless you explicitly added a separator as an argument between each string argument).

A common usage of the CONCAT_WS() function is to create a comma-delimited list.

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

February 14, 2020May 2, 2018 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.

How to Concatenate Strings in SQL Server with CONCAT()

February 14, 2020May 1, 2018 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.

How to Concatenate Strings in MySQL with CONCAT()

February 14, 2020May 1, 2018 by Ian

MySQL has the CONCAT() function, which allows you to concatenate two or more strings. The function actually allows for one or more arguments, but its main use is to concatenate two or more strings.

In MySQL (and in any computer programming environment), string concatenation is the operation of joining character strings end-to-end.

Here’s an example:

SELECT CONCAT('Homer', ' ', 'Simpson') AS 'Full Name';

Result:

+---------------+
| Full Name     |
+---------------+
| Homer Simpson |
+---------------+

Note that I actually concatenated 3 strings here. I concatenated the first name, the last name, plus a space.

What’s the LEN() Equivalent in MySQL?

November 19, 2019May 1, 2018 by Ian

In SQL Server, you can use the LEN() function to return the number of characters in a string. This is a Transact-SQL function that can also be used in Azure databases.

In MySQL, you need to use the CHAR_LENGTH() function.

What’s the DATALENGTH() Equivalent in MySQL?

November 19, 2019April 30, 2018 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.

MySQL length() vs char_length()

November 19, 2019April 30, 2018 by Ian

In MySQL, there are many times where the length() function and the char_length() function will provide exactly the same results. However, there are also times where the results will be completely different. Here’s why.

First, here’s the definition for each of these functions:

char_length(): Returns the length of a string, measured in characters.
length(): Returns the length of a string, measured in bytes.

Notice “characters” vs “bytes” – one is measured in characters, the other is measured in bytes.

In many cases, the number of bytes will be the same as the number of characters in the string, but this isn’t always the case. The number of bytes used per character depends on how the data is stored. For example, if the string is stored as Unicode data, there will be 2 bytes per character.

What is CHAR_LENGTH() in MySQL?

February 14, 2020April 30, 2018 by Ian

In MySQL, the CHAR_LENGTH() function returns the length of a string, measured in characters. MySQL also has CHARACTER_LENGTH(), which is a synonym for CHAR_LENGTH().

Here’s an example:

SELECT CHAR_LENGTH('Lit');

And here’s the result:

+--------------------+
| CHAR_LENGTH('Lit') |
+--------------------+
|                  3 |
+--------------------+