SQL Resources/SQL/Using strings in SQL

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

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,
trimmed
hello
trimmed_
hello
ltrimmed
hello---
rtrimmed
---hello

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,
lpad
-hello
lpad_2
--hello
rpad
hello-

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,
upper
HELLO
lower
hello
initcap
Hello

Reversing

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

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
concat_function
hello world
pipes_operator
hello world

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,
hello
5
hello_b
5
hello_c
5
emoji
1
emoji_b
4
emoji_c
1

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,
starts_with
TRUE
ends_with
TRUE
regexp_contains
TRUE

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

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,
regexp_instr
1
strpos
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.

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

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,
left
he
right
lo
split
[hello, world]
substr
he
regexp_extract
he
regexp_extract_all
[he, lo]

Share SQL code
Write SQL with your team in real-time.
Share SQL code
Write SQL with your team in real-time.