LIKE (Function) / POSITION

-- POSITION: SQL standard function for finding character positions (MySQL, PostgreSQL).
POSITION(search_string IN target_string)

-- INSTR: Arguments are in the opposite order (MySQL, Oracle).
INSTR(target_string, search_string)

-- CHARINDEX: Used in SQL Server.
CHARINDEX(search_string, target_string [, start_position])

-- LOCATE: Equivalent to INSTR (MySQL).
LOCATE(search_string, target_string [, start_position])

-- Check whether an email address contains @.
SELECT
    email,
    POSITION('@' IN email) AS at_position
FROM employees;

-- Use the position of @ to extract the domain part.
SELECT
    email,
    SUBSTRING(
        email,
        POSITION('@' IN email) + 1
    ) AS domain
FROM employees;

-- Retrieve rows where the URL contains a protocol (POSITION > 0 checks for existence).
SELECT url, site_name
FROM external_links
WHERE POSITION('https://' IN url) > 0;

-- Use INSTR to check whether a product name contains a specific brand name (MySQL).
SELECT product_name, unit_price
FROM products
WHERE INSTR(product_name, 'Smart') > 0
ORDER BY unit_price DESC;

-- Position of @ in each email address.
email                   | at_position
------------------------+-----------------
yamada@example.com      | 7
suzuki.h@example.com    | 9
sato123@example.com     | 8

-- Extracted domain part.
email                   | domain
------------------------+-------------
yamada@example.com      | example.com
suzuki.h@example.com    | example.com

'POSITION' is available in MySQL and PostgreSQL (SQL standard syntax).

-- MySQL / PostgreSQL: use POSITION.
SELECT email, POSITION('@' IN email) AS position FROM employees;

In Oracle, use 'INSTR'. The argument order is the opposite of POSITION — you can also pass a third argument for the start position and a fourth for which occurrence to find.

-- Oracle: use INSTR (argument order: target_string, search_string).
SELECT email, INSTR(email, '@') AS position FROM employees;

In SQL Server, use 'CHARINDEX'. The argument order is the same as POSITION: search_string, target_string.

-- SQL Server: use CHARINDEX.
SELECT email, CHARINDEX('@', email) AS position FROM employees;

In SQLite, 'INSTR' is available (argument order is the same as Oracle).

'POSITION' returns character positions starting from 1. If the search string is not found, it returns 0, so you can check for existence with a condition like "greater than 0".

In MySQL, both 'POSITION' and 'INSTR' are available, but their argument orders differ. POSITION takes "search_string IN target_string", while INSTR takes "target_string, search_string". Be careful not to mix them up.

You can combine the returned position with 'SUBSTRING' to flexibly extract substrings based on a specific pattern. For string replacement, see 'UPPER / LOWER / REPLACE'.