Links

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 time_filter()
LIMIT 3;

Query result

lower_firstname
name_strpos
peter
0
brittany
6
christina
9
Last modified 6mo ago