Using strings in SQL

Introduction

Strings are the most flexible column type, able to encode almost any kind of information. For that reason, they are often used as a last resort when storing a complex object. If that happens, you'll have to crack open this article and get ready to do some slicing and dicing before your analysis can start.

String manipulation functionality and syntax differs between databases. In this article the code snippets are written in the Google BigQuery Standard SQL syntax

String manipulation

The operations listed here transform a string into another string - you'll often need to chain several of these transformations together.

Trimming

Trimming a string is the act of stripping characters (often whitespace) from the beginning and end of a string. Whitespace is particularly insidious as it is invisible in most query editors, while remaining very much visible to the database while filtering and grouping.

Common trimming function names are TRIM / LTRIM / RTRIM (the latter for only operating on the start and end of strings respectively).

select
  trim('              hello       ') as trimmed,
  -- Optional character to trim (defaults to whitespace)
  trim('__hello__', '_') as trimmed_,
  ltrim('---hello---', '-') as ltrimmed,
  rtrim('---hello---', '-') as rtrimmed,

Padding

This is the act of adding characters to your string until it reaches the specified length, particularly useful if your database is extra fussy about the format of dates and times. The common function names here are LPAD and RPAD, for adding characters to the beginning and ends of strings respectively.

select
  lpad('hello', 6, '-') as lpad,
  lpad('hello', 7, '-') as lpad_2,
  rpad('hello', 6, '-') as rpad,

Changing case

The common functionality for altering string case are the UPPER / LOWER / INITCAP functions, which do what they say on the tin.

select
  upper('hello') as upper,
  lower('HELLO') as lower,
  initcap('HELLO') as initcap,

Reversing

select
  reverse('racecar') as palindrome,
  reverse('palindrome') as not_a_palindrome

Concatenating

Concatenating, or joining, several strings is a common operation supported with the CONCAT function or sometimes the + or || operators.

select
  concat('hello ', 'world') as concat_function,
  'hello ' || 'world' as pipes_operator

Analysing strings

Length

The length of a string can be surprising - it depends how you count characters. Most letters you'll see in English can be represented as a single byte, but many others cannot (including all emoji).

Therefore most databases can count either the number of visible characters in a string, or the number of bytes required to represent the string. Common function names are LENGTH / CHAR_LENGTH / BYTE_LENGTH / OCTET_LENGTH / DATALENGTH.

select
  length('hello') as hello,
  byte_length('hello') as hello_b,
  char_length('hello') as hello_c,
  length('👪') as emoji,
  byte_length('👪') as emoji_b,
  char_length('👪') as emoji_c,

Comparisons

Exact string comparisons are simple - just use the usual = or != comparison operators.

There are however lots more functions for approximate string comparisons, such as STARTS_WITH / ENDS_WITH / REGEXP_CONTAINS / REGEXP_MATCHES / REGEXP_LIKE.

select
  starts_with('hello', 'h') starts_with,
  ends_with('hello', 'o') ends_with,
  regexp_contains('hello', 'ell') regexp_contains,

Finally there is the LIKE operator which is another, older way to do approximate string comparisons.

with my_strings as (
  select 'a' as string union all
  select 'b' as string union all
  select 'abc' as string
)
-- When using LIKE, the % character is a wildcard
select * from my_strings where string like 'a%'

Substrings

Finding substrings

There are various functions for finding the position of a substring within another string - for example REGEXP_INSTR / STRPOS / POSITION / LOCATE / CHARINDEX.

Make sure you know whether the result is 0-indexed, or 1-indexed - the examples below are 1-indexed.

select
  regexp_instr('hello', 'hell') as regexp_instr,
  strpos('hello', 'hell') as strpos,

Replacing substrings

Useful for cleaning messy string columns, these functions are often called REPLACE or REGEXP_REPLACE. The latter is more flexible as it allows finding substrings using regular expressions.

select
  replace('Hello!', '!', '?') replace,
  regexp_replace('Hello!', 'H|!', '?') regexp_replace,

Extracting substrings

Many functions are available for extracting parts of a string. The most commonly supported are LEFT / RIGHT (for extracting the start and end of a string respectively), and SUBSTR (for extracting a specified chunk of a string).

select
  left('hello', 2) as `left`,
  right('hello', 2) as `right`,
  split('hello,world', ',') as `split`,
  substr('hello', 1, 2) as substr,
  regexp_extract('hello', 'he|lo') as regexp_extract,
  regexp_extract_all('hello', 'he|lo') as regexp_extract_all,