strpos

Applies to VAST Cluster 5.5.

Functions › Search and split › Searching. Returns the position of the first occurrence of a substring in a string, counting characters from 1, or 0 if it doesn't occur. instr is the same function, and position(substring IN s) is the standard SQL form, with the arguments the other way round.

Syntax

strpos(s, substring)
instr(s, substring)
position(substring IN s)

Returns: BIGINT: the position of the first character of the first match, counting from 1, or 0 if there's no match.

Aliases: instr, position(substring IN s).

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_string_strpos. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a table of four users with an e-mail address and a file path. Row 3 has an accented name. The last row has no values:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_string_strpos"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.users"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, email VARCHAR, home VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'ada@example.com', '/home/ada'), "
    "(2, 'grace.hopper@example.org', '/home/grace/db'), "
    "(3, 'jos\u00e9@example.es', '/home/jose'), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

strpos(s, substring) scans s from the left and returns where substring first starts:

• Positions count characters (code points) from 1, not bytes. In 'café x', x is at position 6, although é takes two bytes. A letter written with a combining accent (e followed by U+0301) counts as two.
• The first match wins, and matches can overlap: strpos('aaa', 'aa') is 1, and strpos('abcabc', 'c') is 3.
• No match gives 0, never NULL (unless an argument is NULL). Test strpos(s, substring) > 0 for "contains".
• An empty substring gives 1, even in an empty string.
• Case and accents must match exactly, as in contains: strpos('ABC', 'b') is 0.

position(substring IN s) takes the substring first; strpos and instr take the string first.

NULLs and edge cases

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run("SELECT strpos(NULL, 'o'), strpos('abc', NULL), position(NULL IN 'abc'), position('b' IN NULL)")
run("SELECT strpos('abc', ''), strpos('', ''), strpos('', 'a'), strpos('abc', 'x'), strpos('abc', 'abcd'), "
    "strpos('ABC', 'b'), strpos('aaa', 'aa'), strpos('abcabc', 'c')")
run("SELECT strpos('caf\u00e9 x', 'x'), strpos('e\u0301x', 'x'), strpos('\U0001F1EB\U0001F1F7x', 'x'), "
    "strpos('cafe\u0301', '\u00e9')")
run("SELECT strpos(123, 'o')")

[(None, None, None, None)]
[(1, 1, 0, 0, 0, 0, 1, 3)]
[(6, 3, 3, 0)]
InternalError: Binder Error: No function matches the given name and argument types 'strpos(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

Find the @ in each e-mail address with all three spellings. In row 3, é counts as one character:

run(f"SELECT id, strpos(email, '@'), instr(email, '@'), position('@' IN email) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 4, 4, 4), (2, 13, 13, 13), (3, 5, 5, 5), (4, None, None, None)]

Take the user name before the @, with substring:

run(f"SELECT id, substring(email, 1, strpos(email, '@') - 1) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'ada'), (2, 'grace.hopper'), (3, 'josé'), (4, None)]

Keep the users whose home directory has a subdirectory called db:

run(f"SELECT id, home FROM {TABLE} WHERE strpos(home, '/db') > 0 ORDER BY id LIMIT 10")

[(2, '/home/grace/db')]

How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (VAST_SCAN). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column, so strpos() is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes strpos() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes strpos() and keeps the rows that match. No rows are skipped in storage.

position(substring IN s) appears in the plan as "position"(s, substring). The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE strpos(home, '/db') > 0")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (strpos(home, '/db') > 0) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            home           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• To test whether a string contains a substring, contains() says what you mean. strpos(s, x) > 0 and contains(s, x) both run in the FILTER step; LIKE '%x%' is rewritten to contains().
• Store the part you filter on. If you often split a value at a separator to filter on one part, such as the domain of an e-mail address, write that part to its own column when you load the rows: domain = 'example.org' is pushed down to VAST DataBase. The plan for a raw column compared with a constant has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE email = 'ada@example.com'")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Combine it with a pushed-down condition. In id > 1 AND strpos(home, '/db') > 0, id > 1 is still evaluated in VAST DataBase, so strpos() runs only on the rows that pass it.

Limitations and errors

• Strings only. A number, a date, a boolean, a BLOB or a list on either side fails with No function matches the given name and argument types, for example 'strpos(INTEGER_LITERAL, STRING_LITERAL)'. Cast the value to VARCHAR first.
• No start position. strpos(s, substring, start) fails with No function matches the given name and argument types, and position(substring IN s FROM start) is a syntax error.
• position takes IN, not a comma. position('abc', 'b') fails with Parser Error: syntax error at or near ",".
• Other SQL dialects' names aren't available. locate, charindex and strrpos (the last occurrence) fail with Scalar Function with name ... does not exist!.

run("SELECT strpos('abab', 'b', 3)")
run("SELECT position('abc', 'b')")
run("SELECT locate('b', 'abc')")
run("SELECT strpos(CAST(2026 AS VARCHAR), '2')")

InternalError: Binder Error: No function matches the given name and argument types 'strpos(STRING_LITERAL, STRING_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Parse query: 'SELECT position('abc', 'b')' failed with: Parser Error: syntax error at or near ","
InternalError: Catalog Error: Scalar Function with name locate does not exist!
[(1,)]

Similar functions

run("SELECT strpos('Hello World', 'o'), instr('Hello World', 'o'), position('o' IN 'Hello World'), "
    "contains('Hello World', 'o'), starts_with('Hello World', 'o')")
run("SELECT strpos('caf\u00e9 x', 'x'), strlen('caf\u00e9 '), length('caf\u00e9 ')")

[(5, 5, 5, True, False)]
[(6, 6, 5)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Search and split: all the search functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.