PAT_INDEX_UDF

Description

Returns the starting position of the first occurrence of a pattern in a specified expression, or zero if the pattern is not found, on all valid text and character data types. (SQLServer documentation)

SQLServer syntax

PATINDEX ( '%pattern%' , expression )

Custom UDF overloads

Parameters

1. PATTERN: A varchar expression to evaluate over an expression.

2. EXPRESSION: A varchar expression that is the target of the pattern.

UDF

CREATE OR REPLACE FUNCTION PAT_INDEX_UDF(PATTERN VARCHAR, EXPRESSION VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
AS
$$
  SELECT CHARINDEX(TRIM(PATTERN, '%'), EXPRESSION)
$$;

SQL Server

Query

SELECT position = PATINDEX('%ter%', 'interesting data');

Result

3

Snowflake

Query

SELECT
--** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'PAT_INDEX_UDF' INSERTED. **
PAT_INDEX_UDF('%ter%', 'interesting data') AS position;

Result

POSITION
3

Related EWIs

1. MSCEWI1020: CUSTOM UDF INSERTED.