Cases and Spaces

Exploratory Data Analysis in SQL

Christina Maimone

Data Scientist

Converting case

SELECT lower('aBc DeFg 7-');
abc defg 7-
SELECT upper('aBc DeFg 7-');
ABC DEFG 7-
Exploratory Data Analysis in SQL

Case insensitive comparisons

SELECT * 
  FROM fruit;
 customer | fav_fruit 
----------+-----------
      349 | apple            <- #1
      874 | Apple            <- #2
      703 |  apple           <- #3
      667 | bannana
      622 |  banana
      387 | BANANA
      300 | APPLES           <- #4
      313 | apple            <- #5
      499 | banana
      418 | apple            <- #6
      841 | BANANA
      300 | APPLE            <- #7
      754 | apple            <- #8 
(13 rows)
SELECT * 
  FROM fruit
 WHERE lower(fav_fruit)='apple';
 customer | fav_fruit 
----------+-----------
      349 | apple
      874 | Apple
      313 | apple
      418 | apple
      300 | APPLE
(5 rows)
Exploratory Data Analysis in SQL

Case insensitive searches

-- Using LIKE

SELECT * 
  FROM fruit
 -- "apple" in value
 WHERE fav_fruit LIKE '%apple%';
 customer | fav_fruit 
----------+-----------
      349 | apple
      703 |  apple
      313 | apple
      418 | apple
      754 | apple  
(5 rows)
-- Using ILIKE

SELECT * 
  FROM fruit
 -- ILIKE for case insensitive
 WHERE fav_fruit ILIKE '%apple%';
 customer | fav_fruit 
----------+-----------
      349 | apple
      874 | Apple
      703 |  apple
      300 | APPLES
      313 | apple
      418 | apple
      300 | APPLE
      754 | apple  
(8 rows)
Exploratory Data Analysis in SQL

Watch out!

SELECT fruit 
  FROM fruit2;
   fruit 
------------
 apple
 banana
 pineapple
 grapefruit
 grapes
SELECT fruit 
  FROM fruit2
 WHERE fruit LIKE '%apple%';
   fruit 
------------
 apple
 pineapple
Exploratory Data Analysis in SQL

Trimming spaces

SELECT trim(' abc ');
  • trim or btrim: both ends
    • trim(' abc ') = 'abc'
  • rtrim: right end
    • rtrim(' abc ') = ' abc'
  • ltrim: left start
    • ltrim(' abc ') = 'abc '
Exploratory Data Analysis in SQL

Trimming other values

SELECT trim('Wow!', '!');
 Wow
SELECT trim('Wow!', '!wW');
 o
Exploratory Data Analysis in SQL

Combining functions

SELECT trim(lower('Wow!'), '!w');
 o
Exploratory Data Analysis in SQL

Bring order to messy text!

Exploratory Data Analysis in SQL

Preparing Video For Download...