SQL / TEXT, NUMERIC, AND DATE FUNCTIONS
Searching inside strings and replacing text
Locate a substring's position, test whether it appears, and rewrite text with REPLACE or TRANSLATE without corrupting values you did not mean to touch.
What you will learn
- Read POSITION/STRPOS/INSTR as 1-based offsets, where 0 means the needle is absent
- Test containment with POSITION(x IN col) > 0; >= 0 is true for every row
- Swap every occurrence with REPLACE, or delete text by replacing it with ''
- Predict nested REPLACE order effects, and reach for TRANSLATE for one-pass char maps
Understanding Searching inside strings and replacing text
SQL gives you two separate operations on the inside of a string, and it pays to keep them apart. A search function answers where: POSITION('@' IN email) hands back a 1-based character offset, or 0 when the needle is not there at all. REPLACE answers what would it look like, returning a brand-new string with every match swapped out. Because 0 can never be a real position, that sentinel is what makes POSITION(...) > 0 the idiomatic containment test, and it is also why nothing is ever modified in place: REPLACE produces a value, so keeping the result means wrapping it in an UPDATE ... SET.
The searching function is the least portable thing in this lesson. PostgreSQL and MySQL accept the standard POSITION(needle IN haystack), PostgreSQL also offers STRPOS(haystack, needle), SQLite and Oracle use INSTR(haystack, needle), and SQL Server uses CHARINDEX(needle, haystack). Notice that the argument order flips between the last two, and since both arguments are just strings, a swapped call does not raise an error; it returns 0 for every row, so a WHERE built on it silently matches nothing. Case sensitivity is a property of the collation rather than of the function, which is why the same POSITION call is case-sensitive on PostgreSQL and case-insensitive under MySQL's default utf8mb4_0900_ai_ci.
REPLACE scans left to right and swaps every non-overlapping occurrence in one pass, and the text it writes is not re-examined by that same call. Nest one REPLACE inside another and that protection disappears, because the outer call sees the inner call's output as ordinary input. Replacing with an empty string is deletion, which gives you a counting trick: the drop in length divided by the needle's length is the number of occurrences. TRANSLATE, where it exists, maps single characters through a position-paired list in a single pass, so cascades cannot happen at all.
-- PostgreSQL
WITH contacts(id, email) AS (
VALUES (1, 'ana.silva@old-corp.com'),
(2, 'bruno@example.org'),
(3, 'carla.reis@old-corp.com')
)
SELECT id,
POSITION('@' IN email) AS at_pos,
POSITION('old-corp' IN email) AS domain_pos,
REPLACE(email, 'old-corp.com', 'newcorp.io') AS fixed
FROM contacts
ORDER BY id;Searching reports where a substring starts as a 1-based offset with 0 for absent, and replacing returns a new string with every occurrence changed rather than editing the stored value.
Worked examples
Where it is versus whether it is there
Compares an offset-returning search with a yes/no LIKE test, and shows what collation does to both.
SELECT tag,
POSITION('sql' IN tag) AS pos_lower,
tag LIKE '%sql%' AS like_lower,
POSITION('sql' IN LOWER(tag)) AS pos_folded
FROM (VALUES ('SQL-basics'), ('advanced-sql'), ('nosql-intro')) AS t(tag);Example explained
Line 1POSITION('sql' IN tag) returns 0 for 'SQL-basics' because PostgreSQL's default collation treats 'SQL' and 'sql' as different needles.
Line 2tag LIKE '%sql%' answers only yes or no; it confirms the row matches but can never tell you at which character.
Line 3Folding the haystack with LOWER(tag) makes the search case-insensitive, and the offset stays usable on the original value because lowercasing these strings does not change their character count.
Line 4'nosql-intro' matches at offset 3, a reminder that substring search ignores word boundaries, so filter further if you meant a whole token.
Counting occurrences with a length difference
Uses REPLACE with an empty replacement to count how many times a character appears.
SELECT path,
LENGTH(path) - LENGTH(REPLACE(path, '/', '')) AS slashes
FROM (VALUES ('/usr/local/bin'),
('reports/2026/q1/summary.csv'),
('nofolder')) AS f(path);Example explained
Line 1REPLACE(path, '/', '') deletes every slash rather than just the first, which is exactly why the length difference equals the count.
Line 2For a needle longer than one character you must divide the difference by LENGTH(needle), or the count comes out inflated by that factor.
Line 3The 'nofolder' row yields 0 rather than NULL, because REPLACE with no match returns its input unchanged.
Nested REPLACE cascades, TRANSLATE does not
Shows how a second REPLACE can rewrite the text the first one just produced.
SELECT REPLACE(REPLACE('a-b', 'a', 'b'), 'b', 'c') AS chained,
TRANSLATE('a-b', 'ab', 'bc') AS translated;Example explained
Line 1The inner REPLACE turns 'a-b' into 'b-b', so the outer REPLACE finds two b's: the original one and the one created a moment earlier.
Line 2TRANSLATE pairs 'a'->'b' and 'b'->'c' by position and applies the whole map in one pass, so the b derived from a is never revisited.
Line 3TRANSLATE operates character by character; passing 'ab' does not search for the two-character string 'ab'.
Important notes
POSITION returns NULL, not 0, when either argument is NULL, so WHERE POSITION(...) > 0 quietly drops NULL rows; that is unknown, which is not the same as not found.
TRANSLATE exists in PostgreSQL and Oracle but not in SQLite or MySQL, and wrapping a column in POSITION or REPLACE, like a leading-% LIKE, prevents a plain B-tree index from being used, so containment searches over large tables need a trigram or full-text index.
Common mistakes
Writing WHERE POSITION('@' IN email) >= 0: since 0 is the not-found sentinel, the condition is true for every non-NULL row and the filter does nothing.
Running UPDATE t SET url = REPLACE(url, 'http', 'https') over a mixed column: rows already holding 'https://...' become 'httpss://...' because 'http' also occurs inside 'https', and no error is raised.
Swapping the arguments of INSTR or CHARINDEX: both parameters are strings, so the call succeeds and returns 0 everywhere, which looks like missing data rather than a bug.
Try it yourself
Change, predict, then run
Build a three-row VALUES list of file paths where two of them contain '/2026/', then write one SELECT that returns only those two rows along with the position where '/2026/' starts and the path rewritten with '/2026/' changed to '/current/'.
Open the SQL workspaceCheck your understanding
A column holds a mix of 'http://a.com' and 'https://a.com' values. What happens to the already-secure row when you run UPDATE t SET url = REPLACE(url, 'http://', 'https://')?
- It becomes 'httpss://a.com', because REPLACE matches the leading 'http'
- It raises an error, because the replacement string is longer than the search string
- It is left unchanged, because the literal 'http://' does not occur in 'https://a.com'
- It is rewritten twice, once for 'http' and once for 'https', giving 'httpsss://a.com'
Show answer
REPLACE matches its search argument as one complete literal, and in 'https://a.com' the characters 'http' are followed by 's' rather than '://', so there is no occurrence and the value is returned untouched. The first option is what happens with the shorter needle 'http', which is precisely why including '://' in the search string is what makes this statement safe to re-run.