website logo
⌘K
🏠Homepage
πŸ‘‹Introduction
Key Concepts & Architecture
πŸš€Getting Started
πŸ‘¨β€πŸ’»SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
🚨Error Handling
πŸ†šDifferences Between Oxla vs. PostgreSQL
Docs powered byΒ archbeeΒ 

STRPOS

9min

This article will walk you through the STRPOS() function with its syntax and a few examples.

Overview

The STRPOS() is used to return the position from where the substring (the second argument) is matched with the string (the first argument).

Syntax
|
STRPOS(string, substring)
ο»Ώ

The input and return must be of type string.

πŸ’‘Special cases:

  • Returns NULL if there are no input rows or NULL values.
  • If the substring is not found in the string, then the STRPOS() function will return 0.

Examples

Case 1: Basic STRPOS() function

In the example below, we will find the ut (substring) position in the computer (string):

Select strpos
|
SELECT STRPOS('computer', 'ut') AS "Position of ut";
ο»Ώ

We can see that ut is located at the fifth character of the computer:

Output
|
+-----------------+
| Position of ut  |
+-----------------+
| 5               |
+-----------------+
ο»Ώ

Case 2: STRPOS() function using column

We have a listofwords table where it stores the word data.

Create a Table
|
CREATE TABLE listofwords (
  words string
);
INSERT INTO listofwords 
    (words) 
VALUES 
    ('corral'),
    ('traditionally'),
    ('real'),
    ('communal'),
    ('challenge'),
    ('fall'),
    ('wall'),
    ('gallop'),
    ('albatross');
ο»Ώ
Display the Table
|
SELECT * FROM listofwords;
ο»Ώ

The above query will show the following table:

The listofwords Table
|
+----------------+
| words          |
+----------------+
| corral         |
| traditionally  | 
| real           | 
| communal       | 
| challenge      | 
| fall           | 
| wall           | 
| gallop         | 
| albatross      | 
+----------------+
ο»Ώ

The following query will display the words and a position of a specific substring = β€˜al’ using the STRPOS() function:

Select strpos
|
SELECT words, STRPOS(words, 'al') AS "Position of al"
FROM listofwords;
ο»Ώ

The result will display the al position of different words:

Output
|
+----------------+------------------+
| words          | Position of al   |
+----------------+------------------+
| corral         | 5                |
| traditionally  | 10               |
| real           | 3                |
| communal       | 7                |
| challenge      | 3                |
| fall           | 2                |
| wall           | 2                |
| gallop         | 2                |
| albatross      | 1                |
+----------------+------------------+
ο»Ώ

ο»Ώ

Updated 11 May 2023
Did this page help you?
Yes
No
PREVIOUS
SUBSTR
NEXT
REGEXP_REPLACE
Docs powered byΒ archbeeΒ 
TABLE OF CONTENTS
Overview
Examples
Case 1: Basic STRPOS() function
Case 2: STRPOS() function using column

Dedicated to data analytics that provides 10x faster execution of analytical queries than current state of the art solutions. We are launching SOON! Join the waiting list for more info.




Β©2022 Oxla