An SQL split string operation is database-specific. SQL Server, PostgreSQL, MySQL, and Oracle use different functions and return different shapes. To split string in SQL, choose the syntax for your database engine, then define how ordering, empty tokens, and delimiters should behave.

This SQL string split guide returns usable rows or array elements rather than treating one function as portable standard SQL.

SQL split string: SQL Server STRING_SPLIT, ordinals, and empty tokens

SQL Server uses STRING_SPLIT and normally returns one row per token in a value column. SQL Server 2022 and supported Azure versions can also return an ordinal column by passing 1 as the third argument.

Input and query:

DECLARE @s nvarchar(100) = N’red,,blue’;

SELECT s.ordinal, s.value
FROM STRING_SPLIT(@s, N’,’, 1) AS s
ORDER BY s.ordinal;

The result is ordered rows: red, an empty value, and blue. Without the ordinal option, output order is not guaranteed. Consecutive delimiters produce empty substrings; filter them with WHERE s.value <> N” when they are not meaningful. STRING_SPLIT accepts a single-character separator, so use another method for multi-character delimiters.

PostgreSQL: string_to_array and regexp_split_to_table for arrays or rows

PostgreSQL offers a literal-delimiter function and a regular-expression function. string_to_array returns one array, supports multi-character text delimiters, and preserves empty fields between delimiters.

Array input:

SELECT string_to_array(‘red,,blue’, ‘,’) AS pieces;

To turn that array into ordered rows, use unnest with ordinality:

SELECT x.position, x.piece
FROM unnest(string_to_array(‘red,,blue’, ‘,’)) WITH ORDINALITY AS x(piece, position)
ORDER BY x.position;

For a regular-expression delimiter, use regexp_split_to_table:

SELECT x.piece
FROM regexp_split_to_table(‘red||blue||green’, E’\|\|’) AS x(piece);

This returns rows directly. Regex matching determines the split behavior; zero-length matches at the beginning or end are ignored. Use a pattern that matches the delimiter itself when empty values matter.

MySQL: JSON_TABLE or recursive splitting for rows

MySQL 8.0 has no general-purpose native split function. JSON_TABLE provides a compact row-producing pattern by converting the delimited value to a JSON array. The example below supports a multi-character literal delimiter and preserves empty values.

Input and query:

SET @s = ‘red||||blue’;

SET @delimiter = ‘||’;

SELECT j.item_no, j.value
FROM JSON_TABLE(
CONCAT(‘[‘, REPLACE(JSON_QUOTE(@s), @delimiter, ‘”,”‘), ‘]’),
‘$[*]’ COLUMNS (
item_no FOR ORDINALITY,
value VARCHAR(100) PATH ‘$’
)
) AS j
ORDER BY j.item_no;

The returned rows are red, an empty value, and blue. This conversion treats the delimiter literally, not as a regular expression, and assumes the delimiter does not occur inside a value. For more complex parsing rules, a recursive common table expression can repeatedly locate the delimiter with LOCATE or INSTR and return each piece with its position.

Oracle: REGEXP_SUBSTR for ordered rows and regex delimiters

Oracle can extract the nth token with REGEXP_SUBSTR. REGEXP_COUNT supplies the number of rows, making the result ordered and allowing empty tokens to remain visible as NULL.

Input and query:

SELECT LEVEL AS position,
REGEXP_SUBSTR(‘red,,blue’, ‘([^,]*)(,|$)’, 1, LEVEL, NULL, 1) AS piece
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(‘red,,blue’, ‘,’) + 1
ORDER BY position;

The first capture group returns each token, including the empty middle token; Oracle represents an empty string as NULL. REGEXP_SUBSTR accepts regular-expression delimiters, so the capture pattern can be adapted for multi-character separators such as :: or more complex boundary rules. Keep the row-count expression aligned with that delimiter and use ORDER BY position for deterministic output.

Use the SQL DELETE statement to remove existing rows from a table. Its basic form names the target table, then optionally adds a WHERE condition. Supplying a qualifying condition removes only matching rows; omitting WHERE removes every row in the target table. These are different operations, not interchangeable forms.

A DELETE in SQL statement can select rows by their own column values or by the absence of related rows. A SQL DELETE query using NOT EXISTS is useful when deletion should occur only if no matching record exists in another table.

SQL DELETE statement syntax: table references and affected rows

The minimal SQL DELETE syntax is:

DELETE FROM table_name WHERE condition;

DELETE FROM identifies the table affected by the operation. The optional WHERE clause determines which rows qualify. For example:

DELETE FROM sessions WHERE expires_at < CURRENT_TIMESTAMP;

This removes only sessions whose expiration time has passed. Rows with a future expiration time remain in the table. If no rows satisfy the condition, the statement completes without deleting anything.

When WHERE is omitted, every row in the named table qualifies:

DELETE FROM sessions;

This empties the table’s rows but does not normally remove the table itself, its columns, or its indexes. Because the statement has no row filter, it must not be treated as equivalent to a DELETE with a qualifying condition. A condition that happens to match every current row is still a deliberate filter; an omitted condition provides no row-level protection.

Table references become important when a statement uses more than one table or when aliases make column ownership clearer. A qualified reference such as orders.customer_id identifies the column from orders, rather than relying on an ambiguous unqualified name.

Filtering DELETE in SQL with WHERE and aliases

The WHERE clause can combine equality, ranges, Boolean logic, and null checks. For example, this statement deletes old cancelled orders while preserving recent or active orders:

DELETE FROM orders WHERE status = ‘cancelled’ AND created_at < DATE ‘2024-01-01’;

The database evaluates the condition for each candidate row. Both parts of the AND expression must be true for that row to be deleted. Use parentheses when combining AND with OR, so the intended precedence is explicit.

An alias is useful when the target table appears in a correlated subquery. In systems that support this PostgreSQL-style form, the alias follows the table name:

DELETE FROM customers AS c WHERE c.status = ‘inactive’;

Here, c refers to the row being considered for deletion. Alias syntax varies by database system. For example, SQL Server commonly writes a target alias before the FROM clause:

DELETE c FROM customers AS c WHERE c.status = ‘inactive’;

Check the syntax for the specific database engine before moving a multi-table DELETE into production. The central rule remains the same: the target reference identifies the table, and the WHERE clause limits affected rows.

How SQL NOT EXISTS evaluates a correlated absence test

SQL NOT EXISTS tests whether a subquery returns no rows. It does not compare a value directly. Instead, the database evaluates the subquery as an existence test and returns true when the subquery produces zero matching rows.

A correlated example identifies customers who have no orders:

SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id);

The inner query is correlated because it refers to c.customer_id from the outer query. Logically, the database considers each customer, searches orders for an order with the same customer ID, and applies NOT EXISTS. If at least one related order is found, the condition is false. If none is found, the condition is true and that customer appears in the result.

SELECT 1 is conventional in an existence test. The selected value is irrelevant; only whether a row exists matters. The database can also stop searching after finding the first match. This makes the expression a direct way to represent “no related row satisfies this condition.”

NOT EXISTS also avoids a common null issue associated with some NOT IN expressions. If the subquery can return null, NOT IN may produce an unknown result rather than the expected absence test. The correlated relationship should still use compatible keys and appropriate indexes.

Worked SQL DELETE query examples for selected and unmatched rows

To delete selected rows based on a related table, place the correlated absence test in the DELETE filter. This PostgreSQL-style example removes inactive customers who have never placed an order:

DELETE FROM customers AS c
WHERE c.status = ‘inactive’
AND NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id);

The customer must satisfy both conditions: the status must be inactive, and the subquery must find no order for that customer. An inactive customer with even one matching order is not deleted.

The same pattern can identify unmatched rows before deletion. This query lists staging contacts that have no corresponding approved contact:

SELECT s.contact_id, s.email
FROM staging_contacts AS s
WHERE NOT EXISTS (SELECT 1 FROM approved_contacts AS a WHERE a.email = s.email);

After confirming that the result is correct, the selection can become a DELETE:

DELETE FROM staging_contacts AS s
WHERE NOT EXISTS (SELECT 1 FROM approved_contacts AS a WHERE a.email = s.email);

This removes only staging contacts whose email has no match in approved_contacts. It does not delete approved contacts, and it does not delete staging contacts that do have a match.

For a safer workflow, run the correlated SELECT with the exact intended filter first, inspect the returned keys, and then execute the corresponding DELETE in a transaction when the database supports transactional DML. Also verify that the relationship column is the correct business key; matching on an unrelated or nullable column can classify rows as unmatched incorrectly.