Category: DBMS

Database Management Systems

CHARINDEX() vs PATINDEX() in SQL Server – What’s the Difference?

Posted on by Ian

In SQL Server, you can use either the CHARINDEX() function or the PATINDEX() function to find a string within a string. These are Transact-SQL string functions, and they’re also available on Azure databases.

On the surface, these functions appear to do exactly the same thing, and in many cases, you could use whichever you prefer to use.

However, there a a couple of distinctions that could dictate which function you decide to use in certain scenarios. These can be summarized by the following:

  • PATINDEX() allows you to use wildcard characters to search for patterns. CHARINDEX() doesn’t.
  • CHARINDEX() accepts a third argument which allows you to specify the start position of the search. PATINDEX() doesn’t.

More detail on these points below.

Continue reading →

How to Find a String within a String in SQL Server

Posted on by Ian

In SQL Server, you can use the T-SQL CHARINDEX() function or the PATINDEX() function to find a string within another string. Here’s a quick overview of each function.

The CHARINDEX() Function

This function accepts 3 arguments; the string to find, the string to search, and an optional start position.

The CHARINDEX() syntax goes like this:

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

Where expressionToFind is the expression you want to find in the other string, and expressionToSearch is the other string. The optional start_location can be used to specify a position within expressionToSearch for which to start searching.

Note that only the position of the first occurrence is returned.

Continue reading →

What’s the SQL Server Equivalent of ELT() in MySQL?

Posted on by Ian

In MySQL, you can use the ELT() function to return an item from a specified position in a list. SQL Server has a similar function, but with a different name.

In SQL Server, the CHOOSE() function does basically the same thing that the ELT() function does in MySQL. To be more precise, CHOOSE() is actually a Transact-SQL function, so it can also be used in Azure databases.

Continue reading →

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

Posted on by Ian

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. Continue reading →

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 Return an Argument’s Position within a List of Arguments in MySQL

Posted on by Ian

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.

Continue reading →

How to Format the Date & Time in MySQL

Posted on by Ian

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.

Continue reading →

List of Date Format Specifiers in MySQL

Posted on by Ian

MySQL has a number of functions that allow you to format the date and time. These include functions such as DATE_FORMAT() and TIME_FORMAT().

When using these functions, you provide arguments that tell it what format you’d like the date or time to be in. These arguments are called format specifiers, and they begin with a percentage sign (%) followed by a character (usually a letter, but not always).

The following table lists the format specifiers that can be used when formatting the date and time in MySQL.

Continue reading →