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

STRINGSUBSTRINGOutput

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_firstnamename_strpos

peter

0

brittany

6

christina

9

Last updated