Working on text in SQL

The string toolkit: measure, slice, find, replace, tidy.

SELECT
    email,
    LENGTH(email) AS chars,
    UPPER(SUBSTR(email, 1, 1)) AS initial,
    INSTR(email, '@') AS at_position,
    SUBSTR(email, INSTR(email, '@') + 1) AS domain,
    REPLACE(email, '@', ' at ') AS obscured,
    TRIM(name, '. ') AS tidy_name
FROM customers;

How it works

1. SUBSTR counts from 1, and a negative start counts from the end.
2. INSTR returns the position of a match, or 0 when there is none.
3. TRIM takes an optional set of characters to strip, not just spaces.

Keywords and builtins used here

• AS
• FROM
• LENGTH
• REPLACE
• SELECT
• TRIM
• UPPER
• domain

The run, in numbers

Lines

9

Characters to type

245

Tokens

71

Three-star pace

95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 45 seconds.

Type this snippet

Step 4 of 7 in Expressions, step 19 of 24 in Analytics & reporting.

← Previous Next →