Introduction
Quick Start
Table Design
Data Loading
Data Export
- Export
- Spark Connector
Using StarRocks
Reference
- SQL Reference
  - User Account Management
    - ALTER USER
    - CREATE ROLE
    - CREATE USER
    - DROP ROLE
    - DROP USER
    - GRANT
    - REVOKE
    - SET PASSWORD
    - SET PROPERTY
    - SHOW GRANTS
    - SHOW ROLES
  - Cluster Management
  - DDL
  - DML
  - Data Type
    - Numeric Data Type
      - BIGINT
      - LARGEINT
      - SMALLINT
      - TINYINT
      - BOOLEAN
      - DECIMAL
      - DOUBLE
      - FLOAT
      - INT
    - String Data Type
      - CHAR
      - VARCHAR
      - STRING
    - Date Data Type
      - DATE
      - DATETIME
    - Other Data Tpye
      - HLL
      - BITMAP
  - Auxiliary Commands
    - DESCRIBE
- Function Reference
  - Date Functions
    - convert_tz
    - curdate
    - current_timestamp
    - curtime
    - datediff
    - date_add
    - date_format
    - date_sub
    - date_trunc
    - day
    - dayname
    - dayofmonth
    - dayofweek
    - dayofyear
    - from_days
    - from_unixtime
    - hour
    - minute
    - month
    - monthname
    - now
    - second
    - str_to_date
    - timediff
    - timestampadd
    - timestampdiff
    - to_date
    - to_days
    - unix_timestamp
    - utc_timestamp
    - weekofyear
    - year
  - Geographic Functions
  - String Functions
    - append_trailing_char_if_absent
    - ascii
    - char_length
    - concat
    - concat_ws
    - ends_with
    - find_in_set
    - get_json_double
    - get_json_int
    - get_json_string
    - group_concat
    - instr
    - lcase
    - left
    - length
    - locate
    - lower
    - lpad
    - ltrim
    - money_format
    - null_or_empty
    - regexp_extract
    - regexp_replace
    - repeat
    - reverse
    - right
    - rpad
    - split
    - split_part
    - starts_with
    - strleft
    - strright
  - Aggregation Functions
    - approx_count_distinct
    - avg
    - bitmap
    - count
    - hll_union_agg
    - max
    - min
    - percentile_approx
    - stddev
    - stddev_samp
    - sum
    - variance
    - var_samp
  - Bitmap Functions
  - Array Functions
  - cast function
  - hash function
- System Variable
- Error Code
- System Limit
Administration
- Build in Docker
- Cluster Administration
- Configuration
- Scale Up and Down
- Backup and Restore
- Data Recovery
  - Data Recovery
  - Metadata Recovery
- Replica
- Query Planning
- Profiling
- Monitor and Alert
- User Privilege
- Authentication
- Resource Management
FAQs
- Deploy
- Data Migration
  - Data Ingestion
  - Data Export
Benchmark
- SSB Benchmark
Release Notes
- v1.19
- v2.0

array_position

description

Syntax

array_position(any_array, any_element)

Get the position of the certain element in the array. If yes, it returns position; otherwise, it returns 0.

example

mysql> select array_position(["apple","orange","pear"], "orange");
+-----------------------------------------------------+
| array_position(['apple','orange','pear'], 'orange') |
+-----------------------------------------------------+
|                                                   2 |
+-----------------------------------------------------+
1 row in set (0.01 sec)

You can also get position whether the array contains NULL.

mysql> select array_position([1, NULL], NULL);
+--------------------------------+
| array_position([1,NULL], NULL) |
+--------------------------------+
|                              2 |
+--------------------------------+
1 row in set (0.00 sec)

You can also get position whether it's multi-dimensional array. At this time, you need to ensure that the subarray elements match exactly, including the element arrangement order.

mysql> select array_position([[1,2,3], [4,5,6]], [4,5,6]);
+--------------------------------------------+
| array_position([[1,2,3],[4,5,6]], [4,5,6]) |
+--------------------------------------------+
|                                          2 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select array_position([[1,2,3], [4,5,6]], [4,6,5]);
+--------------------------------------------+
| array_position([[1,2,3],[4,5,6]], [4,6,5]) |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (0.00 sec)

keyword

ARRAY_POSITION,ARRAY

In this article

array_position