POSITION() function in Snowflake?

Explained: POSITION() function in Snowflake?

by

Are you looking to find how to use the POSITION() function within the Snowflake cloud data warehouse or maybe you are looking for a solution, how to use the POSITION function in the Snowflake? If you are looking for any of these problem solutions then you have landed on the correct page. I will also show you whajayden daniels jersey brandon aiyuk youth jersey nike technical cross body bag adidas yeezy 700 v3 balmain carbone fragrantica bose quietcomfort 35 usa price Purchase Iowa rugby uniforms, Iowa olive jerseys, Iowa rugby shoes, and other accessories detske lyžiarske nohavice 134 140 kilpi predam sport jumpsuit nike costume leopardato triangolo Italy köröm díszítő szalagok golf męski zara játék hajszárító árukeresö brandon aiyuk youth jersey brandon aiyuk jersey youth and how to use the POSITION() function. I will explain the POSITION() function by taking a practical example. So don’t waste time let’s start step by step guide to understanding what is the POSITION() expression.blutuszos mennyezeti lámpa lego friends lego friends lego friends חליפות מידות גדולות נשים blutuszos mennyezeti lámpa massage pistol bežecká obuv automatický dávkovač mýdla lidl onlysxm.com team easy on tøj til salg שולחנות מתקפלים maison-metal.com blutuszos mennyezeti lámpa massage pistol panske teplaky panske teplaky

What is POSITION() Function?

POSITION function is used to get the position of the substring present in a string. For example, If we want to get the position of ‘r’ present in ‘car’ then we can make use of this function.

How can we use POSITION() Function ?

POSITION() function is used to find the first occurrence of the first argument in the second argument. If successful then returns the position of the first argument in the second argument.

  • If any one of the value in function is NULL then function will also returns NULL.
  • If Binary or String value is not found then the function returns 0.
  • If the first argument ( expression-1 ) is empty then function returns 1.
  • If the specified Start_postion is beyond the end of the second argument then the function returns 0.

Note: The CHARINDEX function does not support one of the syntax variations that Position supports.

What is the syntax of Position in Snowflake?

-- Syntax : 

POSITION ( Expression-1, Expression-2, Start_position )

POSITION() Argument Details :

Expression-1String or Binary value ( Mandatory )
Expression-2String or Binary value ( Mandatory )
Start_PositionInteger value ( Optional )
Table 1: Position function in Snowflake Argument List

Expression-1 :

A String value or Binary expression represents the value to look for.

Expression-2 :

A String value or Binary expression represents the value to search.

Start_position :

The number indicates where to start the search ( with 1 representing the start of expression-2 ).

Return :

The data type returns the Integer.

Example of Position function in Snowflake :

select position('pl', 'apple', 1);

+----------------------------+
| POSITION('PL', 'APPLE', 1) |
|----------------------------|
|                          3 |
+----------------------------+


select position('le', 'apple', 3);

+----------------------------+
| POSITION('LE', 'APPLE', 3) |
|----------------------------|
|                          4 |
+----------------------------+

As we can see from the above examples, we got the expected output. In example 1 ‘pl’ is present in the 3rd  position and ‘le’ is present in the 4th position

select position(x'EF', x'ABEF');
+----------------------------+
| POSITION(X'EF', X'ABCDEF') |
|----------------------------|
|                          2 |
+----------------------------+

select position(x'BC', x'ABCD');
+--------------------------+
| POSITION(X'BC', X'ABCD') |
|--------------------------|
|                        0 |
+--------------------------+

select position(x'bc', x'AABC');

+--------------------------+
| POSITION(X'BC', X'AABC') |
|--------------------------|
|                        2 |
+--------------------------+

Full Example of POSITION() function in Snowflake?

-- Create a table

create or replace TABLE EMP1 (
	ID NUMBER(38,0),
	NAME VARCHAR(30),
	INFO VARCHAR(20),
);

-- Insert Data

insert into emp(id,name,amount) values 
(1,'Ram'),
(2,'RAJU'),
(3,'Sameer'),
(4,'Sai Kumar'),
(5,'Naveen Sai'),
(6,'Seshi Kumar'),
(7,'Rajiv Ram'),
(8,NULL)


-- Table Data

SELECT * from emp;
+----+-------------+----------+
| ID | NAME        | INFO     |
|----+-------------+----------|
|  1 | Ram         | NOT-NULL |
|  2 | RAJU        | NOT-NULL |
|  3 | Sameer      | NOT-NULL |
|  4 | Sai Kumar   | NOT-NULL |
|  5 | Naveen Sai  | NOT-NULL |
|  6 | Seshi Kumar | NOT-NULL |
|  7 | Rajiv Ram   | NOT-NULL |
|  8 | NULL        | NOT-NULL |
+----+-------------+----------+

-- Applying position to the table data

select id,name,position('a',NAME,1) as position from emp;
+----+-------------+----------+
| ID | NAME        | POSITION |
|----+-------------+----------|
|  1 | Ram         |        2 |
|  2 | RAJU        |        0 |
|  3 | Sameer      |        2 |
|  4 | Sai Kumar   |        2 |
|  5 | Naveen Sai  |        2 |
|  6 | Seshi Kumar |       10 |
|  7 | Rajiv Ram   |        2 |
|  8 | NULL        |     NULL |
+----+-------------+----------+

When you should use POSITION() Function in Snowflake?

There are certain use case scenarios when it is recommended to use the POSITION function within the Snowflake cloud data warehouse which are as follows:

  • If we want to get the position of first occurance of the substring or expression from the input string.

Real World Use Case Scenarios for POSITION Function in Snowflake

  • If we want to get the position of the customer present in a queue.
  • In hospital we will get a token based on first come first serve, In token we will be having a position to proceed.

POSITION() Snowflake Official Documentation Link

Final Thoughts

In this article, we have learned about POSITION() function and uses with the examples explained clearly. I have also covered different scenarios with a practical example that could be possible. I hope the information that was provided is helped in gaining the knowledge.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

Join Telegram Group
Download Free Azure Videos
Connect on LinkedIn

Revanth Kumar