SQL LIKE, IN and BETWEEN with wildcards and ranges

#Why it matters

SQL LIKE, IN and BETWEEN are how you turn "find the cancellations", "exclude postage lines" and "sales in November" into filters. They look trivial, and that is the danger: a stray underscore, a case-sensitive match or a BETWEEN on a timestamp gives a plausible but wrong number with no error. These filters appear in almost every analyst screening test, usually with exactly those traps built in.

#The concept

Examples use retail.duckdb (UCI Online Retail, built in SQL data types and CAST: numbers, text, dates) and taxi.duckdb (built in What is SQL? Databases, tables and your first query).

#LIKE and its two wildcards

% and _ are wildcards, so to match a literal % or _ you escape it and name the escape character: like '%\_%' escape '\'. Underscores are common in codes (gift_0001_20), and an unescaped _ matches any character, so like '%_%' matches every non-empty string.

#Case sensitivity

SQLite is case-insensitive for ASCII letters by default. Know which you are on before you trust a zero.

#LIKE and indexes

A B-tree index is sorted, like a phone book. like 'SET OF%' (fixed prefix) can jump to the right page. like '%SET OF%' (leading wildcard) cannot, because the match could be anywhere, so the database reads every row. For "contains" searches on big tables, PostgreSQL offers trigram indexes (pg_trgm) and full-text search. In PostgreSQL with a non-C collation, even prefix like needs an index built with text_pattern_ops.

#IN

stock_code in ('POST', 'DOT', 'M') is shorthand for stock_code = 'POST' or stock_code = 'DOT' or stock_code = 'M'. With a subquery, customer_id in (select customer_id from ...) keeps rows whose value appears in the subquery result.

not in has the NULL trap from SQL NULL values: IS NULL, COALESCE and three-valued logic: if the list or subquery contains a NULL, not in returns no rows. And x not in (...) never matches a row where x itself is NULL.

#BETWEEN is inclusive on both ends

x between a and b means x >= a and x <= b. For integers that is fine. For timestamps it causes two bugs:

1. Missing the last day. invoice_ts between '2011-11-01' and '2011-11-30' means up to 2011-11-30 00:00:00, so every sale after midnight on 30 November is lost.

2. Double-counting the boundary. Daily reports built as between day and next_day count a row at exactly midnight in two days.

#Half-open ranges: the safe default

where invoice_ts >= '2011-11-01' and invoice_ts < '2011-12-01'

Start inclusive, end exclusive. It works for date, timestamp and timestamptz, needs no "23:59:59.999" guessing, never overlaps with the next period, and can use an index on invoice_ts because the column is not wrapped in a function.

#Regular expressions

When % and _ are not enough (a pattern like "five digits then optional letters"), use regex:

#Trailing spaces

'BATHROOM METAL SIGN' and 'BATHROOM METAL SIGN ' look the same on screen. In PostgreSQL and DuckDB varchar comparison they are different values, so group by description gives two groups. SQL Server (and MySQL collations with the PAD SPACE attribute) ignore trailing spaces in =, while like and group by may not. char(n) columns pad with spaces by design. Trim on load, or trim before comparing.

#Worked example: cleaning filters on Online Retail

#Cancellations

Invoice numbers starting with C are cancellations:

select count(*) as lines, count(distinct invoice) as invoices
from lines where invoice like 'C%';

┌───────┬──────────┐
│ lines │ invoices │
│ int64 │  int64   │
├───────┼──────────┤
│  9288 │     3836 │
└───────┴──────────┘

#Codes that are not products

Product stock codes are five digits, sometimes with a letter suffix (85123A). Everything else is a fee or an adjustment. Find them with regex first, rather than guessing a list:

select count(*) filter (where regexp_matches(stock_code, '^[0-9]{5}[A-Za-z]*$'))     as product_codes,
       count(*) filter (where not regexp_matches(stock_code, '^[0-9]{5}[A-Za-z]*$')) as other_codes
from lines;

┌───────────────┬─────────────┐
│ product_codes │ other_codes │
│     int64     │    int64    │
├───────────────┼─────────────┤
│        538914 │        2995 │
└───────────────┴─────────────┘

Listing the other_codes shows POST, DOT, M, C2, D, S, BANK CHARGES, AMAZONFEE, CRUK, party bags and gift vouchers. The fee and adjustment codes go into an in list:

select stock_code, description, count(*) as lines
from lines
where stock_code in ('POST', 'DOT', 'M', 'AMAZONFEE', 'BANK CHARGES', 'C2', 'D', 'S', 'CRUK')
group by all order by lines desc;

┌──────────────┬─────────────────┬───────┐
│  stock_code  │   description   │ lines │
│   varchar    │     varchar     │ int64 │
├──────────────┼─────────────────┼───────┤
│ POST         │ POSTAGE         │  1252 │
│ DOT          │ DOTCOM POSTAGE  │   709 │
│ M            │ Manual          │   571 │
│ C2           │ CARRIAGE        │   143 │
│ D            │ Discount        │    77 │
│ S            │ SAMPLES         │    63 │
│ BANK CHARGES │ Bank Charges    │    37 │
│ AMAZONFEE    │ AMAZON FEE      │    34 │
│ CRUK         │ CRUK Commission │    16 │
│ POST         │ NULL            │     4 │
│ DOT          │ NULL            │     1 │
│ C2           │ NULL            │     1 │
└──────────────┴─────────────────┴───────┘

select count(*) as lines, sum(quantity * unit_price) as value
from lines
where stock_code in ('POST', 'DOT', 'M', 'AMAZONFEE', 'BANK CHARGES', 'C2', 'D', 'S', 'CRUK');

┌───────┬───────────────┐
│ lines │     value     │
│ int64 │ decimal(38,3) │
├───────┼───────────────┤
│  2908 │    -34587.249 │
└───────┴───────────────┘

These 2,908 lines net to minus 34,587: large Amazon fees and manual adjustments outweigh postage income. Excluding them changes "product revenue" by that amount, which is exactly the kind of number you state next to your answer.

#The underscore wildcard

select count(*) filter (where stock_code like '%_%')              as underscore_wildcard,
       count(*) filter (where stock_code like '%\_%' escape '\')  as literal_underscore,
       count(*) as all_lines
from lines;

┌─────────────────────┬────────────────────┬───────────┐
│ underscore_wildcard │ literal_underscore │ all_lines │
│        int64        │       int64        │   int64   │
├─────────────────────┼────────────────────┼───────────┤
│              541909 │                 34 │    541909 │
└─────────────────────┴────────────────────┴───────────┘

Unescaped, _ matched every line. Escaped, it finds the 34 gift voucher lines (gift_0001_10 to gift_0001_50).

#Case: LIKE vs ILIKE

select count(*) filter (where description like '%postage%')  as like_lower,
       count(*) filter (where description ilike '%postage%') as ilike,
       count(*) filter (where description like '%POSTAGE%')  as like_upper
from lines;

┌────────────┬───────┬────────────┐
│ like_lower │ ilike │ like_upper │
│   int64    │ int64 │   int64    │
├────────────┼───────┼────────────┤
│          0 │  1961 │       1961 │
└────────────┴───────┴────────────┘

In DuckDB and PostgreSQL, the lower-case pattern finds nothing. The same like in MySQL and SQL Server would match:

-- MySQL 8.4
select 'POSTAGE' like '%postage%' as mysql_like, 'POSTAGE' like binary '%postage%' as binary_like;

mysql_like	binary_like
1	0

Descriptions containing "SET OF" in any case: description ilike '%set of%' returns 29,881 lines, the same as the upper-case like, because this shop writes descriptions in capitals. Use ilike anyway; the next data source will not be so tidy.

#Leading wildcards and indexes

The same table in PostgreSQL with an index on description (built with text_pattern_ops):

explain select count(*) from lines where description like 'SET OF%';
explain select count(*) from lines where description like '%SET OF%';

 Aggregate  (cost=8394.92..8394.93 rows=1 width=8)
   ->  Bitmap Heap Scan on lines  (cost=365.73..8329.15 rows=26310 width=0)
         Filter: (description ~~ 'SET OF%'::text)
         ->  Bitmap Index Scan on lines_description_idx  (cost=0.00..359.16 rows=25873 width=0)
               Index Cond: ((description ~>=~ 'SET OF'::text) AND (description ~<~ 'SET OG'::text))
 
 Finalize Aggregate  (cost=11500.25..11500.26 rows=1 width=8)
   ->  Gather  (cost=11500.03..11500.24 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=10500.03..10500.04 rows=1 width=8)
               ->  Parallel Seq Scan on lines  (cost=0.00..10462.44 rows=15035 width=0)
                     Filter: (description ~~ '%SET OF%'::text)

The prefix search became an index range (>= 'SET OF' and < 'SET OG'). The contains search reads the whole table. You will learn to read plans properly in the query performance module; for now, notice the words "Index" and "Seq Scan".

#Trailing spaces split one product into two

select '"' || description || '"' as quoted, count(*) as lines
from lines
where trim(description) in ('BATHROOM METAL SIGN', 'COLUMBIAN CANDLE ROUND', 'FRENCH PAISLEY CUSHION COVER')
group by description order by trim(description);

┌─────────────────────────────────┬───────┐
│             quoted              │ lines │
│             varchar             │ int64 │
├─────────────────────────────────┼───────┤
│ "BATHROOM METAL SIGN"           │   641 │
│ "BATHROOM METAL SIGN "          │    73 │
│ "COLUMBIAN CANDLE ROUND "       │    31 │
│ "COLUMBIAN CANDLE ROUND"        │    64 │
│ "FRENCH PAISLEY CUSHION COVER " │   105 │
│ "FRENCH PAISLEY CUSHION COVER"  │    18 │
└─────────────────────────────────┴───────┘

Wrapping values in quotes is the quickest way to see invisible spaces. Across the table, 112,626 lines have a trailing space, and trimming reduces 4,223 distinct descriptions to 4,211.

#November: half-open vs BETWEEN

select count(*) as nov_half_open
from lines where invoice_ts >= '2011-11-01' and invoice_ts < '2011-12-01';

select count(*) as nov_between_30
from lines where invoice_ts between '2011-11-01' and '2011-11-30';

┌───────────────┐
│ nov_half_open │
│     int64     │
├───────────────┤
│         84711 │
└───────────────┘
┌────────────────┐
│ nov_between_30 │
│     int64      │
├────────────────┤
│          81257 │
└────────────────┘

between lost 3,454 lines: all of 30 November after 00:00. (cast(invoice_ts as date) between '2011-11-01' and '2011-11-30' also returns 84,711, but wrapping the column in a cast can stop an index being used.)

The double-count shows up on the taxi data, where trips start around the clock:

select count(*) as jan2_between
from trips where tpep_pickup_datetime between '2021-01-02' and '2021-01-03';

select count(*) as jan2_half_open
from trips where tpep_pickup_datetime >= '2021-01-02' and tpep_pickup_datetime < '2021-01-03';

┌──────────────┐
│ jan2_between │
│    int64     │
├──────────────┤
│        34310 │
└──────────────┘
┌────────────────┐
│ jan2_half_open │
│     int64      │
├────────────────┤
│          34309 │
└────────────────┘

One trip started at exactly 2021-01-03 00:00:00, so between counted it on 2 January and will count it again on 3 January. In January 2021, 15 trips start at exactly midnight on 13 different days. A daily report built with between adds 15 phantom trips to the month.

#Common mistakes

• Unescaped _ in a pattern. like 'gift_%' also matches giftX...; like '%_%' matches everything. Escape it.

• Assuming LIKE is case-insensitive because it was in MySQL or SQL Server. In PostgreSQL and DuckDB it is not. Use ilike or lower().

• not in with a nullable list. Returns nothing. Use not exists.

• between with timestamps. Loses the last day or double-counts midnight. Use >= start and < end.

• Wrapping the column instead of the constant. where year(invoice_ts) = 2011 or cast(invoice_ts as date) = ... can prevent index use; write the range on the raw column.

• Comparing text with invisible spaces. Quote values in output to see them, and trim at load time.

#Interview angle

#Practice task

On retail.duckdb:

1. Count lines and distinct customers for cancellation invoices, and the total value they reverse.

2. Find every distinct description that contains "CHRISTMAS" in any case and does not contain "CARD".

3. Count December 2010 lines with a half-open range, then with between '2010-12-01' and '2010-12-31', and explain the difference.

4. Find stock codes that match ^[0-9]{5}$ (no suffix) and stock codes with a suffix, and give one example of each pair that shares the same five digits (for example 85123A and 85123, if both exist).

-- 1
select count(*) as lines, count(distinct customer_id) as customers,
       sum(quantity * unit_price) as value
from lines where invoice like 'C%';

-- 2
select distinct description
from lines
where description ilike '%christmas%' and description not ilike '%card%'
order by description;

-- 3
select count(*) from lines where invoice_ts >= '2010-12-01' and invoice_ts < '2011-01-01';
select count(*) from lines where invoice_ts between '2010-12-01' and '2010-12-31';

-- 4
select s.stock_code as with_suffix, b.stock_code as base
from (select distinct stock_code from lines where regexp_matches(stock_code, '^[0-9]{5}[A-Za-z]+$')) s
join (select distinct stock_code from lines where regexp_matches(stock_code, '^[0-9]{5}$')) b
  on left(s.stock_code, 5) = b.stock_code
limit 5;

#Further reading

• PostgreSQL: pattern matching: like, ilike, similar to and POSIX regex in one page.

• DuckDB: pattern matching: like, ilike, glob and regex functions.

• PostgreSQL: operator classes (text_pattern_ops): why prefix like needs a special index under most collations.

• Use The Index, Luke: LIKE filters: leading wildcards and index ranges explained with diagrams.