STRPOS

Returns the starting position of the first instance of a given substring within a string.

Syntax

STRPOS(STRING, SUBSTRING)

Arguments

STRING

Type: string

A sequence of characters to search.

SUBSTRING

Type: string

The substring to search for.

Returns

Type: bigint

Returns the starting position of the first instance of SUBSTRING in STRING.

Positions start from 1. If no instance is found, 0 is returned.

---

Examples

STRING	SUBSTRING	Output
All happy families are alike	all	0
All happy families are alike	All	1
All happy families are alike	are	20
All happy families are alike	''	1
''	''	1
''	word	0
null	word	null

Transformation job example

SQL

CREATE JOB function_operator_example
    ADD_MISSING_COLUMNS = true	
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data 
  MAP_COLUMNS_BY_NAME
    SELECT 
        LOWER(customer_firstname) AS lower_firstname,
        STRPOS(LOWER(customer_firstname), 'a') AS name_strpos
    FROM default_glue_catalog.upsolver_samples.orders_raw_data 
    WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
    LIMIT 3;

Query result

lower_firstname	name_strpos
peter	0
brittany	6
christina	9