Text Functions in Excel Versus SQL String Functions

Text cleanup is what most workbooks do with data nobody wants to handle: split a name, trim an id, rebuild a code, take the domain from an email address. SQL has the same functions under similar names, and the differences sit in the details that matter most for untidy data: spaces, nulls and case. This post maps the common functions with worked results, and flags where a straight translation gives a different answer. It is a spoke of translating Excel formulas into SQL.

The direct matches

Take the string 'Ada Lovelace', which has 12 characters: the letters of Ada, a space at position 4 and Lovelace from position 5 to 12.

Positions start at 1 in both worlds, which removes the usual source of off-by-one errors when translating between a spreadsheet and a programming language. SUBSTITUTE and replace are both case-sensitive and replace every occurrence: the two lowercase a characters, at positions 3 and 10, become o, and the capital A is untouched. Excel's optional fourth argument, the instance number, has no direct SQL counterpart; replacing only the second occurrence needs a regular expression or string surgery.

A subtle difference in SEARCH versus FIND: Excel's FIND is case-sensitive, and SEARCH is not and accepts wildcards. strpos is case-sensitive like FIND. To mimic SEARCH("LOVE", A1), which returns 5, lower both sides: strpos(lower(s), lower('LOVE')).

TRIM does more in Excel

=TRIM(" Ada Lovelace ") returns Ada Lovelace. Excel's TRIM removes leading and trailing spaces and also reduces runs of spaces inside the text to a single space. SQL's trim removes only the ends:

SELECT trim('  Ada   Lovelace ');

 Ada   Lovelace

The three inner spaces survive. To match Excel, collapse whitespace with a regular expression and trim the result:

SELECT btrim(regexp_replace('  Ada   Lovelace ', '\s+', ' ', 'g'));

 Ada Lovelace

regexp_replace(..., '\s+', ' ', 'g') replaces every run of whitespace with one space, and btrim removes the space left at either end. This is the single most useful translation in text cleanup, because untidy data is full of double spaces that make joins fail (see VLOOKUP and XLOOKUP versus SQL JOIN for what happens to keys). Two further traps. Excel's TRIM removes only the ordinary space character (code 32), and SQL's \s also matches tabs and line breaks, which is usually an improvement. And neither touches the non-breaking space (code 160) that comes from web pages and PDFs; remove it explicitly with replace(s, chr(160), ' ') in PostgreSQL, CHAR(160) in SQL Server, before trimming. Excel users meet the same problem when TRIM seems not to work.

CLEAN removes non-printing characters. In PostgreSQL, regexp_replace(s, '[\x00-\x1f]', '', 'g') strips control characters.

Joining text and the NULL trap

=A2 & " " & B2 joins two cells. If B2 is blank, Excel treats it as empty text and returns the first value plus a space. SQL's || operator returns NULL if either side is null:

SELECT 'Ada' || ' ' || NULL;           -- NULL
SELECT concat('Ada', ' ', NULL);       -- 'Ada '
SELECT concat_ws(' ', 'Ada', NULL);    -- 'Ada'

concat ignores nulls, and concat_ws (concatenate with separator) ignores nulls and does not put a separator next to a missing value, which is usually what a full-name formula wants: concat_ws(' ', first_name, last_name) returns just the first name when the last name is missing. In SQL Server, the + operator behaves like || in PostgreSQL by default (null in, null out), and CONCAT and CONCAT_WS are available. Oracle is different again: it treats an empty string as NULL, so '' || 'x' is 'x' and a test col = '' is never true. Know which engine you are on before you translate a blank check.

TEXTJOIN(", ", TRUE, A2:A10) joins the cells of a range with a separator and skips blanks. Across rows of a table, that is an aggregate:

SELECT string_agg(name, ', ' ORDER BY name)
FROM (VALUES ('Linus'), ('Ada'), (NULL), ('Grace')) AS t(name);

 Ada, Grace, Linus

string_agg skips nulls, as TEXTJOIN does with its TRUE argument. MySQL calls it GROUP_CONCAT, SQL Server STRING_AGG, and some engines need the order specified inside the call, as above, because without it the order is arbitrary.

Splitting

Excel splits text with LEFT, MID and FIND combined, with TEXTSPLIT in recent versions, or with Data, Text to Columns. In SQL, split_part returns the nth piece of a delimited string:

SELECT split_part('Ada Lovelace', ' ', 1) AS first_name,
       split_part('Ada Lovelace', ' ', 2) AS last_name,
       split_part('[email protected]', '@', 2) AS domain;

 first_name | last_name |    domain
------------+-----------+--------------
 Ada        | Lovelace  | example.test

The email domain is the Excel formula =MID(A1, FIND("@", A1) + 1, 99) made simple. string_to_array('a,b,c', ',') returns an array, and regexp_split_to_table returns one row per piece, which is what you want to split one cell into many rows (something Excel does poorly). split_part returns an empty string when there is no such piece, not an error. MySQL has SUBSTRING_INDEX, SQL Server has STRING_SPLIT for splitting into rows, and neither has a split_part in every version.

Case: PROPER and friends

PROPER("ada lovelace") capitalizes the first letter of each word. The PostgreSQL function is initcap:

SELECT initcap('ada lovelace'), initcap('O''BRIEN'), initcap('mcdonald');

   initcap    |  initcap  |  initcap
--------------+-----------+----------
 Ada Lovelace | O'Brien   | Mcdonald

Both treat any non-letter as a word boundary and lowercase the rest, so O'BRIEN becomes O'Brien and names with internal capitals (McDonald) become Mcdonald. Neither is a reliable way to fix people's names; it is a way to make a list uniform. Fix real names by hand or from a source that has them right. Engines without initcap (SQL Server, MySQL) need a user function or a different approach.

Excel's EXACT(a, b) compares case-sensitively. Excel's = is case-insensitive, which is the reverse of PostgreSQL, where = is case-sensitive and lower(a) = lower(b) mimics Excel.

Numbers and dates as text

TEXT(value, format) turns a number or date into text with a format code. The codes do not carry over; each engine uses its own pattern language:

In to_char, the FM prefix removes the padding spaces PostgreSQL adds by default; without it the result has a leading space. SQL Server uses FORMAT(value, 'N2') or CONVERT styles, and MySQL uses FORMAT(1234.5, 2) and DATE_FORMAT. VALUE depends on the locale of the machine in Excel (decimal comma versus point); the SQL cast does not, and fails on '19,99'. Decide the format of text numbers before converting, and parse with an explicit rule.

Length and bytes

LEN counts characters. length in PostgreSQL counts characters too. In MySQL, LENGTH counts bytes and CHAR_LENGTH counts characters, so a string with accents has a longer LENGTH than CHAR_LENGTH. SQL Server's LEN ignores trailing spaces, and DATALENGTH counts bytes. If the workbook relies on LEN to validate widths (a code must be 5 characters), use the character-counting function of your engine and test a value with an accent and a trailing space.

Verify

Normalized text is also what makes removing duplicates and date functions behave. Test on the ugliest strings in the data: double spaces, trailing spaces, non-breaking spaces, accents, empty strings and nulls. Compare each translated column with the workbook's cached values. The strings where the two disagree show you which of the traps above the workbook was relying on, often without its author knowing.

When a text formula is better left in the spreadsheet

It is a one-off cleanup. For a list you will clean once, Data, Text to Columns and Flash Fill are quicker than any query.

It builds a label for a chart or a print. Presentation text belongs in the report layer.

The logic is fuzzy. Splitting free-text addresses or extracting amounts from comments is a parsing problem. Do it in code with tests, not in either formulas or a long SQL expression.

The data is not in a database. Loading a column to trim it is more work than TRIM.

PostgreSQL documents these in its string functions reference, and Excel's functions are listed in Microsoft's text functions reference. To check a small extract as JSON, use the CSV to JSON converter.