CHARINDEX

Description

Returns the index of the first occurrence of the specified value sent as a parameter when it matches (CHARINDEX in Transact-SQL).

Sample Source Pattern

Syntax

SQL Server

CHARINDEX( expression_to_find, expression_to_search [, start] )

Snowflake SQL

Snowflake SQL complete documentation

CHARINDEX( <expr1>, <expr2> [ , <start_pos> ] )

JavaScript

JavaScript complete documentation

String.indexOf( search_value [, index] )

Examples

SQL Server

Code:

IN -> SqlServer_01.sql

SELECT CHARINDEX('t', 'Customer') AS MatchPosition;

Result:

      INDEX|      
-----------+
         33|

Snowflake SQL

Code:

OUT -> SqlServer_01.sql

SELECT
CHARINDEX('t', 'Customer') AS MatchPosition;

Result:

      INDEX|      
-----------+
         33|

JavaScript

Indexes in Transact start at 1, instead of JavaScript which start at 0.

Code:

CREATE OR REPLACE FUNCTION get_index
(
  expression_to_find varchar, 
  expression_to_search varchar, 
  start_index  float
)
  RETURNS float
  LANGUAGE JAVASCRIPT
AS
$$
  return EXPRESSION_TO_SEARCH.indexOf(EXPRESSION_TO_FIND, START_INDEX)+1;
$$;

SELECT GET_INDEX('and', 'Give your heart and soul to me, and life will always be la vie en rose', 20) AS INDEX;

Result:

      INDEX|      
-----------+
         33|