SQL String Functions

Text literals use single quotes: 'active'. Double quotes mean an identifier (a column or table name), which is a common beginner mix-up.

Common functions

Cleaning and combining

select first_name || ' ' || last_name        as full_name,
       lower(trim(email))                   as email_clean,
       split_part(email, '@', 2)            as email_domain
from   customers

Case-insensitive matching

-- normalize both sides
where lower(state) = 'pa'

-- or use ILIKE (DuckDB / Postgres)
where state ilike 'pa'

Text types and lengths

In DuckDB, TEXT, STRING, CHAR(n), and VARCHAR(n) are all the same type, VARCHAR. The length is accepted and ignored: a varchar(5) column stores eight characters without complaint, and a char(5) column doesn't pad 'ab' with spaces.

create or replace table text_types (
  a text,
  b char(5),
  c varchar(5),
  d string
);
insert into text_types values ('x', 'ab', 'abcdefgh', 'y');
select length(b) as b_length,
       c,
       typeof(c) as c_type
from text_types;

length counts characters, and strlen counts the bytes they take in UTF-8. They differ for any character outside plain ASCII. An emoji with a skin tone is two characters to length and one to length_grapheme, which counts what a reader sees as one symbol:

select length('héllo')           as chars,
       strlen('héllo')           as bytes,
       length('👍🏽')            as thumbs_chars,
       length_grapheme('👍🏽')   as thumbs_symbols,
       strlen('👍🏽')            as thumbs_bytes;

That returns 5, 6, 2, 1, and 8. A limit like "a code is at most 10 characters" means length; a limit on storage or a fixed-width file means bytes.

Comparing and sorting text

= compares text exactly. 'Tools ' with a trailing space doesn't equal 'Tools', and 'tools' doesn't either. Clean both sides with trim and lower, or compare with collate nocase:

select 'Tools ' = 'Tools'                  as trailing_space,
       'tools' = 'Tools'                   as different_case,
       trim('Tools ') = 'Tools'            as trimmed,
       'tools' = 'Tools' collate nocase    as nocase;

ORDER BY on text sorts by character code, so every uppercase letter comes before every lowercase one, and accented letters come after z. collate nocase.noaccent sorts the way a reader expects:

select name
from (values ('zebra'), ('Éclair'), ('apple'), ('Banana')) as t(name)
order by name;

select name
from (values ('zebra'), ('Éclair'), ('apple'), ('Banana')) as t(name)
order by name collate nocase.noaccent;

The first returns Banana, apple, zebra, Éclair. The second returns apple, Banana, Éclair, zebra.

Close matches and patterns

levenshtein(a, b) counts the single-letter edits that turn one string into the other, and jaro_winkler_similarity(a, b) scores them from 0 to 1. Both compare characters exactly, so lowercase both sides first:

select first_name,
       last_name,
       levenshtein(lower(last_name), 'castelano') as edits
from customers
where levenshtein(lower(last_name), 'castelano') <= 2

Both Castellanos come back, 1 edit away. A close match is a candidate, not proof: Ford and Foss are only 2 edits apart. For text with a shape rather than a spelling, such as "digits, then letters", see SQL regular expressions.

How other databases differ

So the same query can match a row in SQL Server or MySQL and miss it in DuckDB. When text comes from another system, trim it and settle its case once, as it loads.