how to

How to Find a List Item at a Specified Position in MySQL

by

In MySQL, you can use the ELT() function to return a list item at a given position in the list.

The syntax goes like this:

ELT(N,str1,str2,str3,...)

Where N is the position of the item you want to return, and str1,str2,str3,... is the list.

Example

Here’s an example:

SELECT ELT(3, 'Marge', 'Homer', 'Bart') AS 'Who is at 3?';

Result:

+--------------+
| Who is at 3? |
+--------------+
| Bart         |
+--------------+

In this case we specify that we want to return the 3rd item in the list (because the first argument is 3) . And in this case the list is 'Marge', 'Homer', 'Bart', so the 3rd item is Bart.

Read more

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

by

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.

Read more

How to Return an Argument’s Position within a List of Arguments in MySQL

by

In MySQL, you can use the FIELD() function to return the position of a given string or number within a list of arguments. The function returns the index (position) of the first argument in the list of subsequent arguments.

The syntax goes like this:

FIELD(str,str1,str2,str3,...)

Where str is the item you want to find, and str1,str2,str3,... is the list you’re searching through.

Here’s an example:

SELECT FIELD('Homer', 'Marge', 'Homer', 'Bart') AS 'Where is Homer?';

Result:

+-----------------+
| Where is Homer? |
+-----------------+
|               2 |
+-----------------+

In this example, the list is: 'Marge', 'Homer', 'Bart' and we’re searching for the string Homer within that list. And because Homer is the 2nd item in the list of arguments, the result is 2.

Read more

How to Format the Date & Time in MySQL

by

In MySQL, the DATE_FORMAT() function allows you to format the date and time.

Here’s an example:

SELECT DATE_FORMAT('2018-12-01', '%W, %d %M %Y');

Result:

Saturday, 01 December 2018

In this example, %W is for the weekday name, %d is for the day of the month, %M is for Month, and %Y is for Year. There are many more format specifiers available that enable you to specify a precise format for dates, as well as the time component.

Read more

How to Format Numbers in SQL Server

by

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.

Read more

How to Format the Date & Time in SQL Server

by

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.

Read more

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

by

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.

Read more

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

by

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.

Read more

How to Concatenate Strings in SQL Server with CONCAT()

by

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.

Read more