Strings

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

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

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).

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.

Changing case

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

Concatenating

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

Analysing strings

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.

Comparisons

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

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.

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

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.

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

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

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

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.

Extracting substrings

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

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
  concat('hello ', 'world') as concat_function,
  'hello ' || 'world' as pipes_operator

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,

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

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%'

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

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

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,