how to – Page 123

How to Remove the Trailing Spaces after the Month Name in Oracle

November 8, 2021 by Ian

If you’ve ever used the TO_CHAR() function to format a date that uses the month name, you might have noticed that sometimes the month is returned with right padding.

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.

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 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.

2 Ways to Add Leading Zeros in PostgreSQL

November 3, 2021 by Ian

In PostgreSQL, we can use the TO_CHAR() function to add leading zeros to a number. The function converts the number to a string, using the (optional) format we specify.

Another option is to use the LPAD() function to pad a number with leading zeros.

How to Add Leading & Trailing Zeros in Azure SQL Edge

November 3, 2021 by Ian

Azure SQL Edge uses a limited version of the SQL Server Database Engine. One of the limitations of Azure SQL Edge, is that it doesn’t currently support CLR-dependent T-SQL functions, such as the FORMAT() function.

This can be a problem when trying to format numbers.

However, there are often ways around such limitations. Here are examples of how we can pad numbers with leading and trailing zeros in SQL Edge.

Add Leading & Trailing Zeros in SQL Server

November 3, 2021 by Ian

Some DBMSs have an LPAD() and RPAD() function which can be used to pad numbers with leading and trailing zeros.

SQL Server doesn’t have such a function. But that doesn’t prevent us from being able to pad numbers with leading/trailing zeros.

How to Remove “X Rows Selected” in SQLcl & SQL*Plus (Oracle)

November 2, 2021 by Ian

When using SQLcl or SQL*Plus with Oracle Database, you may have noticed feedback at the bottom of your query results that tells you how many rows were selected. For example, 100 rows selected (or however many rows were returned).

If you want to get rid of this, you can use SET FEEDBACK OFF.

You also have the option of setting a row threshold, which allows you to specify how many rows should be returned before feedback is provided.