Articles Cookbook
29 Mar 2018
Ignacio L. Bisso

Extracting Data From a String: SPLIT_PART in PostgreSQL

Learn how to use split_part in PostgreSQL to extract data from strings. Quite often, we’d like to extract parts of a string when working with text values. A common example is when we have a full name and need to retrieve only the last name. In this article, we’ll examine how to do it using split_part in PostgreSQL, i.e. a string-related function that can be used to extract a substring.
Read more
27 Feb 2018
Jeffrey Edison

How to Start Thinking in SQL

Thinking in SQL will help you understand what SQL queries do, how to create and change them, and how to create reports from the data you obtain. If you want to know how to write better SQL queries, you have to change the way you think about the code. One of the greatest skills you can have in SQL programming is the ability to change an existing SQL query so you get the results you need.
Read more
9 Feb 2018
Ignacio L. Bisso

How to Remove Junk Characters in SQL

Unwanted characters in text data can be a bit of a pain, but there’s an easy way to fix them. Scroll down to learn how to remove junk characters in SQL in the easiest way! Sometimes, we’ll find unwanted characters inside our string data because our SQL queries didn’t work as expected. Moreover, these extra characters may sometimes be invisible, which really complicates things. In this article, we’ll examine some string-related SQL functions that can handle unwanted characters—visible or not!
Read more
31 Jan 2018
Ignacio L. Bisso

How to Solve Capitalization Data Quality Issues

Misspelled names, typos, and text data quality issues in your database? Power up your queries! Use SQL string functions to address data quality issues related to capitalization. Sometimes, our SQL queries don't work as expected because of data quality issues. In this article, we will examine some string-related SQL functions that can correct data quality issues related to capitalization. We'll be using PostgreSQL in our examples, but similar functions are available in most database engines.
Read more
5 Jan 2018
Aldo Zelen

18 Useful Important SQL Functions to Learn ASAP

Learning a new programming language can seem intimidating. Like any other language, a programming language has a large vocabulary that you need to master. In this article, we’ll look at some of the most useful SQL functions that you need to know. Structured Query Language, commonly known as SQL, is the standard language for managing and querying data in relational databases. Born out of the need to efficiently interact with large datasets, SQL has become an indispensable tool for database administrators, data analysts, and developers alike.
Read more
21 Dec 2017
Aldo Zelen

How to Draw a Christmas Tree in SQL

You can use SQL to manipulate all kinds of data, from huge analytical queries to brief single-purpose statements. But you can also use SQL just for fun, without any business requirements stifling your creativity. So, get out your jolly hat and prepare to sing O Christmas Tree as we create some quirky art with plain old SQL. Today, we’re going to generate some holiday-themed ASCII art, just for fun. That’s right.
Read more
15 Dec 2017
Dejan Sarka

Statistics in SQL: Dependencies Between Discrete Variables

In my previous article, we looked at how you can calculate linear dependencies between two continuous variables with covariance and correlation. Both methods use the means of the two variables in their calculations. However, mean values and other population moments make no sense for categorical (nominal) variables. For instance, if you denote "Clerical" as 1 and "Professional" as 2 for an occupation variable, what does the average of 1.5 signify?
Read more
12 Dec 2017
Aldo Zelen

Simplify SQL Code: Recursive Queries in DBMS

Hey SQL users! Are you repeating the same query in every report? Are your queries getting too complicated? Use recursive queries to simplify SQL code! Too many SQL reports can lead to clutter on your desktop and in your head. And is it really necessary to code each of them separately? Ad-hoc queries can share much of the same SQL code with managerial reports and even regulatory reports. Suppose you’ve been writing basic SQL code for a while.
Read more
7 Dec 2017
Ignacio L. Bisso

The SQL Coalesce Function: Handling Null Values

You may already know how to return null values in SQL. Now, we’re going to learn how to do the opposite. Though the SQL COALESCE function may seem complex, it’s actually very straightforward and useful. Let’s look at several examples of how the SQL COALESCE function can be used to work with NULL values in SQL. The Need for Coalesce in SQL Before we dive into the SQL COALESCE function in detail, you should understand how NULL values behave in expressions.
Read more
29 Nov 2017
Kacper Skawina

Analyzing Survey Data: Explore Results Using SQL

Excel is a powerful beast that lets you analyze complex data. Yet, operating on big chunks of data can sometimes be a daunting task. Let's take a look at how SQL can help. Today, we'll tackle a common problem with importing data to an SQL database, using a real-life example. Suppose your company conducted a survey on the most popular programming trends and preferences, striving to meet the expectations of its users.
Read more