String functions: SQL Server vs Oracle

SQL Server and Oracle offer the same string operations under different names, and the small differences between them are what breaks ported code. SQL Server measures a string with LEN, cuts it with SUBSTRING, searches it with CHARINDEX and joins strings with + or CONCAT; Oracle uses LENGTH, SUBSTR, INSTR and ||. The two search functions take their arguments in opposite order, position 0 means something different to each, LEN leaves out trailing spaces, and Oracle stores an empty string as NULL, which changes both lengths and joins. Lists differ as well: STRING_AGG and LISTAGG turn rows into one string under different size limits, and LIKE understands character classes only in SQL Server.

Every result on this page came from SQLite 3.49.1 running inside sql.js 1.14.2, and Run repeats the query in your browser. SQLite’s string functions resemble both dialects, so each script labels its columns with the SQL Server or Oracle call they stand for, and the rules of each engine are quoted from its manual.

The functions side by side

Most rows are a change of name, which is why ports start well. The trouble sits in four details: what counts toward a length, where a position starts, which argument comes first, and what a NULL part does to a joined string.

Length, bytes and the empty string

SQL Server’s manual defines LEN as the number of characters without trailing spaces and points to DATALENGTH for the number of bytes. Oracle’s LENGTH counts every character of a VARCHAR2 value, trailing spaces included, and LENGTHB counts bytes. The script measures four values:

-- How long is a string? SQL Server's LEN and DATALENGTH and Oracle's LENGTH, modelled with SQLite's length and
-- octet_length. SQLite counts characters (code points) and stores text as UTF-8.
--   len               LEN(s): characters, without trailing spaces
--   oracle_length     LENGTH(s): characters, trailing spaces included; NULL for '' because Oracle stores '' as NULL
--   utf8_bytes        DATALENGTH(s) of varchar under a _UTF8 collation, or Oracle's LENGTHB(s) in an AL32UTF8 database
--   nvarchar_bytes    DATALENGTH(s) of nvarchar: 2 bytes per character for every character used here
WITH samples (label, s) AS (
  VALUES ('code with 3 trailing spaces', 'abc   '),
         ('empty string', ''),
         ('French name', 'Zoë'),
         ('Tamil word', 'தமிழ்')
)
SELECT label,
       length(rtrim(s, ' ')) AS len,
       length(nullif(s, '')) AS oracle_length,
       octet_length(s) AS utf8_bytes,
       2 * length(s) AS nvarchar_bytes
FROM samples;

┌─────────────────────────────┬─────┬───────────────┬────────────┬────────────────┐
│            label            │ len │ oracle_length │ utf8_bytes │ nvarchar_bytes │
├─────────────────────────────┼─────┼───────────────┼────────────┼────────────────┤
│ code with 3 trailing spaces │ 3   │ 6             │ 6          │ 12             │
│ empty string                │ 0   │               │ 0          │ 0              │
│ French name                 │ 3   │ 3             │ 4          │ 6              │
│ Tamil word                  │ 5   │ 5             │ 15         │ 10             │
└─────────────────────────────┴─────┴───────────────┴────────────┴────────────────┘

The code with three trailing spaces has a LEN of 3 and an Oracle LENGTH of 6, so a check such as LEN(code) = 3 accepts values that Oracle reports as six characters long. The empty string has a length of 0 in SQL Server and no length at all in Oracle: the manual says that Oracle treats a character value of length zero as NULL, and the length of NULL is NULL. Bytes depend on the encoding. The Tamil word has 5 characters, 15 bytes in UTF-8 and 10 bytes as nvarchar, so a column sized in bytes fills up long before one sized in characters.

Cutting and searching

SUBSTRING and SUBSTR both count from 1, so four characters from position 5 are the same text in both engines. Position 0 is where they part. SQL Server’s manual says that a start below 1 still uses up length for the positions before the first character, while Oracle’s manual says that a position of 0 is treated as 1. A negative position counts from the end of the string, but only in Oracle.

-- Cutting and searching one product code, modelled with SQLite's substr and instr.
SELECT substr('SKU-2041-BLUE', 5, 4) AS from_5_take_4,   -- SUBSTRING(s, 5, 4) and SUBSTR(s, 5, 4) agree: '2041'
       substr('SKU-2041-BLUE', 0, 4) AS sqlserver_from_0, -- SUBSTRING(s, 0, 4): positions 0 to 3, so only 3 characters
       substr('SKU-2041-BLUE', 1, 4) AS oracle_from_0,    -- SUBSTR(s, 0, 4): Oracle reads position 0 as 1, so 4 characters
       substr('SKU-2041-BLUE', -4) AS oracle_last_4;      -- SUBSTR(s, -4): a negative position counts from the end

-- CHARINDEX(find, s) names the text to find first; Oracle's INSTR(s, find) names it second.
WITH codes (s) AS (VALUES ('SKU-2041-BLUE'))
SELECT instr(s, '-') AS first_hyphen,                                     -- CHARINDEX('-', s) or INSTR(s, '-')
       instr(substr(s, instr(s, '-') + 1), '-') + instr(s, '-') AS second_hyphen, -- INSTR(s, '-', 1, 2) in Oracle
       substr(s, 1, instr(s, '-') - 1) AS prefix,                         -- the text before the first hyphen
       instr('-', s) AS arguments_swapped                                 -- INSTR('-', s): looks for s inside '-'
FROM codes;

┌───────────────┬──────────────────┬───────────────┬───────────────┐
│ from_5_take_4 │ sqlserver_from_0 │ oracle_from_0 │ oracle_last_4 │
├───────────────┼──────────────────┼───────────────┼───────────────┤
│ 2041          │ SKU              │ SKU-          │ BLUE          │
└───────────────┴──────────────────┴───────────────┴───────────────┘
┌──────────────┬───────────────┬────────┬───────────────────┐
│ first_hyphen │ second_hyphen │ prefix │ arguments_swapped │
├──────────────┼───────────────┼────────┼───────────────────┤
│ 4            │ 9             │ SKU    │ 0                 │
└──────────────┴───────────────┴────────┴───────────────────┘

SUBSTRING(s, 0, 4) returned three characters and SUBSTR(s, 0, 4) four, a difference that appears as a missing or an extra character in every row. The search functions differ in argument order: CHARINDEX('-', s) names the text to find first, INSTR(s, '-') names the string to search first, and a port that keeps the old order returns 0 for every row without any error. Oracle’s INSTR also takes an occurrence, so INSTR(s, '-', 1, 2) finds the second hyphen, which SQL Server reaches by nesting one CHARINDEX inside another as its start position.

Joining strings, and NULL

SQL Server’s + returns NULL when any part is NULL, because CONCAT_NULL_YIELDS_NULL is ON, which its manual says is always the case from SQL Server 2017. CONCAT turns a NULL into an empty string, and CONCAT_WS skips a NULL part together with its separator. Oracle’s manual says that its || joins a NULL like an empty string, so the result is NULL only when every part is NULL.

-- One address from parts that may be missing (NULL), joined the way each engine joins text.
CREATE TABLE addresses (id INTEGER PRIMARY KEY, flat TEXT, street TEXT NOT NULL, city TEXT NOT NULL);
INSERT INTO addresses (id, flat, street, city) VALUES
  (1, 'Flat 4B', 'Lake Road', 'Pune'),
  (2, NULL, 'Hill Street', 'Kochi');

SELECT id,
       -- SQL Server: flat + ', ' + street + ', ' + city is NULL as soon as one part is NULL (SQLite's || does the same)
       flat || ', ' || street || ', ' || city AS plus_operator,
       -- CONCAT turns NULL into an empty string, so the separator after it stays
       concat(flat, ', ', street, ', ', city) AS concat_function,
       -- CONCAT_WS skips the NULL part together with its separator
       concat_ws(', ', flat, street, city) AS concat_ws_function,
       -- Oracle: || treats NULL as an empty string, like CONCAT
       coalesce(flat, '') || ', ' || street || ', ' || city AS oracle_pipes
FROM addresses
ORDER BY id;

┌────┬──────────────────────────┬──────────────────────────┬──────────────────────────┬──────────────────────────┐
│ id │      plus_operator       │     concat_function      │    concat_ws_function    │       oracle_pipes       │
├────┼──────────────────────────┼──────────────────────────┼──────────────────────────┼──────────────────────────┤
│ 1  │ Flat 4B, Lake Road, Pune │ Flat 4B, Lake Road, Pune │ Flat 4B, Lake Road, Pune │ Flat 4B, Lake Road, Pune │
│ 2  │                          │ , Hill Street, Kochi     │ Hill Street, Kochi       │ , Hill Street, Kochi     │
└────┴──────────────────────────┴──────────────────────────┴──────────────────────────┴──────────────────────────┘

The second address lost everything with +, kept a stray leading comma with CONCAT and with Oracle’s ||, and came out clean only with CONCAT_WS. Numbers behave differently too. In SQL Server, 'Room ' + 101 fails, because int ranks above varchar in data type precedence and the engine tries to turn the text into a number; Oracle’s 'Room ' || 101 converts the number and returns Room 101. In SQL Server, use CONCAT, which converts its arguments to strings, or an explicit CAST.

Your turn

-- Return id, pos and method for every item of every list, empty items included, numbered from 1.
-- This version returns only the first item of each list.
SELECT id, 1 AS pos, substr(methods, 1, instr(methods || ',', ',') - 1) AS method
FROM payments
ORDER BY id, pos;

# Sample tests: each one builds the payments table, runs your query and compares the rows it returns, in order.
tests:
  - name: a list with an empty third item
    ordered: true
    setup: |
      CREATE TABLE payments (id INTEGER PRIMARY KEY, methods TEXT NOT NULL);
      INSERT INTO payments (id, methods) VALUES (1, 'card,wallet,,cash');
    expected:
      columns: [id, pos, method]
      rows:
        - [1, 1, 'card']
        - [1, 2, 'wallet']
        - [1, 3, '']
        - [1, 4, 'cash']
  - name: a single item, and a list that ends with an empty item
    ordered: true
    setup: |
      CREATE TABLE payments (id INTEGER PRIMARY KEY, methods TEXT NOT NULL);
      INSERT INTO payments (id, methods) VALUES (5, 'cash'), (6, 'card,');
    expected:
      columns: [id, pos, method]
      rows:
        - [5, 1, 'cash']
        - [6, 1, 'card']
        - [6, 2, '']

Lists into one string and back, with failures reproduced Pro: opens with a Pro, Premium or Ultimate pass

Patterns, cleaning and conversions, with numbers Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• LEN leaves out trailing spaces, Oracle’s LENGTH counts them, and Oracle stores an empty string as NULL.
• CHARINDEX(find, s) and INSTR(s, find) take their arguments in opposite order, and position 0 differs between SUBSTRING and SUBSTR.
• SQL Server’s + returns NULL for a NULL part; CONCAT, CONCAT_WS and Oracle’s || do not.
• STRING_AGG and LISTAGG have byte limits, and a split must keep empty items to keep their positions.