SELECT knowledge FROM sql_resources WHERE category='sql' AND slug='strings'

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

trimmed	trimmed_	ltrimmed	rtrimmed
hello	hello	hello---	---hello

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.

lpad	lpad_2	rpad
-hello	--hello	hello-

Changing case

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

upper	lower	initcap
HELLO	hello	Hello

palindrome	not_a_palindrome
racecar	emordnilap

Concatenating

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

concat_function	pipes_operator
hello world	hello world

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.

hello	hello_b	hello_c	emoji	emoji_b	emoji_c
5	5	5	1	4	1

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,

Loading code...

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.

starts_with	ends_with	regexp_contains
true	true	true

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

string
a
abc

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,

Loading code...

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,

Loading code...

regexp_instr	strpos
1	1

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.

replace	regexp_replace
Hello?	?ello?

Extracting substrings

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

Loading code...

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

left	right	split	substr	regexp_extract	regexp_extract_all
he	lo	hello,world	he	he	he,lo

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

Loading code...

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,

Loading code...

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

Loading code...

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

Loading code...

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

Loading code...

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

Loading code...

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,

Loading code...