In SQLite, we can use the IF EXISTS clause of the DROP TABLE statement to check whether the table exists or not before dropping it.
Category: SQLite
5 Ways to Check a Column’s Data Type in SQLite
In SQLite, there are quite a few ways to look at a table’s structure. Therefore, there are quite a few ways we can check the data type of the columns within that table.
There’s also a function that allows us to check the data type of a column returned in a query.
Here are five ways to check the data type of a column in SQLite.
Continue readingHow to Format Numbers with Commas in SQL
Most of the major RDBMSs have functions that enable us to format numbers with commas as either the group separator, or the decimal separator.
Some RDBMSs also output certain numeric data types with commas in the relevant place.
Below are examples of formatting numbers with commas in some of the more popular DBMSs.
Continue readingPRAGMA table_list in SQLite
In SQLite, the table_list pragma returns information about the tables and views in the schema.
It was first introduced in SQLite version 3.37.0 (released on 2021-11-27).
Continue readingFormat a Number as a Percentage in SQL
There are several ways to format a number as a percentage in SQL, depending on the DBMS being used.
Here are examples of adding a percent sign to a number in some of the most popular DBMSs.
Continue reading4 Ways to List the Views in an SQLite Database
Here are four options for showing all views within an SQLite database.
Continue readingFormat a Number as Currency in SQL
Some DBMSs have functions that allow us to format numbers as currency just by passing the appropriate format string. This converts the number to a string with the applicable currency symbol, group separator, and decimal point (if relevant).
Other DBMSs don’t make it that easy, and you need to do a bit of work first.
Below are examples of using SQL to format numbers as currency in some of the most popular DBMSs.
Continue readingConcatenate a String and a Number in SQL
In most cases, concatenating a string and a number in SQL is no different to concatenating two strings.
Most DBMSs will concatenate the string and number as if they were both strings. This is because the number is usually converted to a string before the concatenation operation.
Continue readingHow to Check if a Table Exists in SQLite
In SQLite, we can query the sqlite_schema table to find out whether a given table exists.
Prior to SQLite version 3.33.0, this table was referred to as sqlite_master (it can still be referred to as such in subsequent releases for backwards compatibility).
Find a String within a String in SQL
Most of the major DBMSs provide us with a way to find a string within a string using SQL. By this, I mean use a SQL query to find the position of a substring within a string.
There are several SQL functions that allow us to do this, including INSTR(), LOCATE(), POSITION(), and CHARINDEX(). The function you use will depend on your DBMS, and possibly whether or not you need to specify a starting position.