Introduction to using SQL with strings

Load in the necessary packages

library(tidyverse)
library(odbc)
library(DBI)
library(RSQLite)

create a fake dataset in R using tribble()

original_table<-tribble(~country,~points,
                        "US", 5,
                        "U.S.",6,
                        "US (United States)",7,
                        "us",8,
                        "  US",9,
                        "in Zimbabwe",10,
                        "Zimba.bwe",12,
                        "Great Zimbabwe",10,
                        "Zimbabwean",7,
                        "ZIMBABWE",5,
                        "zimbabwe country",9)
original_table

Set up a database using the datafile

window<-dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(window,original_table)

common issues

• ‘apple’ $neq$ ‘Apple’
• ’’ $\neq$ NULL
• ’ apple’ $\neq$ ‘apple’

SELECT *
FROM original_table;

the main goal here would be to consolidate the above character strings since the following can be noted in our dataset:

• typing errors
• capitalised names and non capitalised names .
• same name with different characters typed
• spaces and dots

REPLACE()

• The REPLACE() function is a versatile function that allows you to replace or remove characters from a string. The syntax is as follows:

REPLACE(fieldName, 'searchFor', 'replaceWith')

SELECT country , REPLACE(country,'.','') AS country_cleaned,points
FROM original_table;

great way to start , we can clearly compare the two columns and note that we have changed U.S. to US and Zimba.bwe to Zimbabwe

• if we use this result with a SUM and GROUP BY it kinda reduces our burden though not that much.

SELECT REPLACE(country,'.','') AS country_cleaned,
       SUM(points) AS points
FROM original_table
GROUP BY country_cleaned;

great ,but still we have a long way to go. whats going on with US? ,are the strings the same?.. no.

TRIM()

• as we have noted above ,the other US has blank spaces inbetween, TRIM allows us to remove unnecessary blank spaces. Let us use TRIM and REPLACE together with aggregate functions

SELECT country , 
       REPLACE(TRIM(country),'.','') AS country_cleaned,
       SUM(points) AS total_points
FROM original_table
GROUP BY country_cleaned;

its now like a step in the right direction , the only problem now is ,us and US(United States)

UPPER() and LOWER()

• These change strings to capital or small letters , Let us use UPPER(), TRIM and REPLACE together with aggregate functions .

SELECT UPPER(country) AS country_cleaned ,points
FROM original_table;

we nolonger have us character ,do we?… naah

SELECT country , 
       REPLACE(TRIM(UPPER(country)),'.','') AS country_cleaned,
       SUM(points) AS total_points
FROM original_table
GROUP BY country_cleaned;

By using the above QUERY ,Zimba.bwe and Zimbabwe now turned to ZIMBABWE while US,us, US and U.S. changed to US but our problem is not yet solved because US(United States) has not been included.

SUBSTR(string,val1,val2) ,RIGHT and LEFT

since we are using SQLite in R we tend to use SUBSTR() and not LEFT or RIGHT

SELECT SUBSTR(country,1,2) AS country_cleaned,points
FROM original_table;

• Let us use SUBSTR(), UPPER(), TRIM() and REPLACE() together with aggregate functions

SELECT SUBSTR(TRIM(REPLACE(UPPER(country),'.','')),1,2) AS country_cleaned,
        SUM(points) AS points
FROM original_table
GROUP BY country_cleaned;

We awe almost safe now since we have managed to combine some strings into US only but lets see if we can wrap it all together and solve our problem

LIKE

• the LIKE clause can be used in a WHERE CLAUSE to search for a pattern in a column.

the % wildcard will match zero,one or many characters in text.

• let us wrap all this up in a CASE statement.

SELECT CASE 
    WHEN SUBSTR(TRIM(REPLACE(UPPER(country),'.','')),1,2) = 'US' 
    THEN 'United States'
    WHEN LOWER(country) LIKE '%zimb%'
    THEN 'Zimbabwe' END AS country_cleaned,
        SUM(points) AS total_points
FROM original_table
GROUP BY country_cleaned;

Great