Ian – Page 208

INSTR() Equivalent in SQL Server

November 8, 2021 by Ian

Many RDBMSs have an INSTR() function that enables us to find a substring within a string. Some (such as MySQL and MariaDB) also have a LOCATE() function and a POSITION() function (also supported by PostgreSQL), that do a similar thing.

SQL Server doesn’t have an INSTR() function. Nor does it have a LOCATE() or POSITION() function. But it does have the CHARINDEX() function that does the same thing.

SQL Server also has the PATINDEX() function, which does a similar job to CHARINDEX().

3 Ways to Separate the Year, Month, and Day from a Date in MariaDB

November 7, 2021 by Ian

MariaDB has several functions that enable you to extract various date and time parts from date/time values. You can use these to separate each date/time component into its own column if required.

Below are three ways to extract the year, month, and day from a date value in MariaDB.

GROUP_CONCAT() Function in MySQL

November 7, 2021 by Ian

MySQL has a GROUP_CONCAT() function that enables us to return columns from a query as a delimited list.

It returns a string result with the concatenated non-NULL values from a group.

LISTAGG() Function in Oracle

November 7, 2021 by Ian

In Oracle, the LISTAGG() function enables us to combine data from multiple rows in to a single row.

We have the option of specifying a separator (such as a comma). We can also order the results produced by the LISTAGG() function, and more.

MariaDB GROUP_CONCAT()

November 6, 2021November 6, 2021 by Ian

MariaDB has a GROUP_CONCAT() function that enables us to return columns from a query as a delimited list.

STRING_AGG() Function in PostgreSQL

November 6, 2021 by Ian

In PostgreSQL, we can use the STRING_AGG() function to return columns from a query as a delimited list.

How to Show Null Values When Running Queries in psql (PostgreSQL)

November 6, 2021November 5, 2021 by Ian

By default, null values are returned as an empty string in psql. But this can easily be changed.

One reason you might want to change this is to avoid null values being confused with actual empty strings.

You can change this with the \pset null 'value' command.

How POSITION() Works in PostgreSQL

November 5, 2021 by Ian

PostgreSQL has a POSITION() function that returns the first starting index of a specified substring within a string.

If the substring doesn’t exist in the string, then zero is returned.

How to Format Numbers with Leading Zeros in SQLite

March 11, 2022November 4, 2021 by Ian

In SQLite, we can use the PRINTF() function or FORMAT() function to format numbers with leading zeros.

How to Pad a Number with Leading Zeros in MariaDB

November 4, 2021 by Ian

MariaDB has an LPAD() function that allows us to pad the left part of a string or number with our chosen character or series of characters.

We can use this function on numeric values in order to add leading zeros.