INSERT() function in Snowflake?

Explained: INSERT() function in Snowflake?

by

Are you looking to find how to use the INSERT() function within the Snowflake cloud data warehouse or maybe you are looking for a solution, how to use the INSERT functions in the Snowflake? I will also show you what and how to use the INSERT function. I will explain the INSERT function by taking a practical example. So nike air max 1 ultra moire black white ราคา mallas para hombre nike kallax korkekiilto hylly blogspot isolateur cloture electrique ruban puma suede classic velvet sneakers in cordovan leather calfskin velvet tongue and toe cap bose quietcomfort 35 usa price Purchase Florida state seminars jerseys, football, and various accessories for Florida state seminars dänisches bettenlager lounge set дамски памучен чорапогащник corsair ddr3 1600 miroir terzo cheap jordan 4 Bonnets rugby corner aiyuk jersey meilleur lampe uvdon’t waste time let’s start step by step guide to understanding what is the INSERT expression.blutuszos mennyezeti lámpa bogner overal lepetitartichaut.com blutuszos mennyezeti lámpa strømper str 42 maison-metal.com Mens VANS 2020 panske teplaky blutuszos mennyezeti lámpa Mens VANS 2020 lepetitartichaut.com bezecke topanky bežecká obuv bogner overal bežecká obuv

What is INSERT() Function?

INSERT() function consists of four arguments that need to be filled mandatorily as shown below. For example, If we want to insert ‘n’ after ‘c’ of a given string ‘abcd’, then we can make use of this function.

How can we use INSERT() Function?

INSERT() Function will be used to INSERT one or more strings/binary values as per the user requirement. If any of the argument values are null then the result value will also be null. Examples are also provided which will help you to understand in a better way. Here the Base_expression and Insert_Expression should be of the same data type.

What is the syntax of INSERT() function?

-- Syntax : 

INSERT( Base_Expression, Position, Length, Insert_Expression )

INSERT argument details:

Base_Expression ( First Argument )String or Binary value (Mandatory)
Position ( Second Argument )Integer value (Mandatory)
Length ( Third Argument )Integer value (Mandatory)
Insert_Expression ( Fourth Argument )String or Binary value (Mandatory)
Table 1: Insert function in Snowflake Argument List

Base_Expression ( First Argument ) :

This Argument needs to be filled with Binary or String expressions that we wanted to Insert/Replace.

Position ( Second Argument ) :

  • This is the offset at which we need to insert the characters.
  • Here we are using 1-based, not 0-based. That means the first character should be considered as at the position-1, not in the position-0.
  • The values that are valid are from 1 to one more that the length of the string ( Base_Expression )

Length ( Third Argument ) :

  • The number of characters that you want to replace in the string, starting at Position.
  • If the value is zero (0), it means to add new characters without deleting the existing characters.

Insert_Expression ( Fourth Argument ) :

The string that should be inserted into the Base_expression.

If the string is empty and If the length is greater than zero then some characters will be deleted ( None will be added ).

INSERT function example in Snowflake:

-- Example 1 : 

SELECT INSERT('xyz',1,1,'uvw')

-- Query Result

+-------------------------+
| INSERT('XYZ',1,1,'UVW') |
|-------------------------|
| uvwyz                   |
+-------------------------+

In the below example we can observe that the empty insert_expression is deleting the pointed characters as displayed below.

-- Example 2 : 

SELECT INSERT('xyz',2,1,'');

-- Query Result

+----------------------+
| INSERT('XYZ',2,1,'') |
|----------------------|
| xz                   |
+----------------------+ 


-- Example 3 : 

SELECT INSERT('xyz',4,0,'a');

-- Query Result

+----------------------+
| INSERT('XYZ',4,0,'A')|
|----------------------|
| xyza                 |
+----------------------+

Here are a few examples that clearly show that, If we are having NULL in any of the arguments then the final result will also be null.

-- Examples :
-- #1

SELECT INSERT(null,2,1,'a');

+----------------------+
| INSERT(NULL,2,1,'A') |
|----------------------|
| NULL                 |
+----------------------+

-- #2

SELECT INSERT('xyz',null,1,'a');

+--------------------------+
| INSERT('XYZ',NULL,1,'A') |
|--------------------------|
| NULL                     |
+--------------------------+

-- #3

SELECT INSERT('xyz',2,null,'a');

+--------------------------+
| INSERT('XYZ',2,NULL,'A') |
|--------------------------|
| NULL                     |
+--------------------------+

-- #4

SELECT INSERT('xyz',2,1,null);

+--------------------------+
| INSERT('XYZ',2,1,NULL)   |
|--------------------------|
| NULL                     |
+--------------------------+

Full Example of INSERT 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 Insert to the table data

select id,name,insert(name,1,0,'abc') as insert_ from emp;
+----+-------------+----------------+
| ID | NAME        | INSERT_        |
|----+-------------+----------------|
|  1 | Ram         | abcRam         |
|  2 | RAJU        | abcRAJU        |
|  3 | Sameer      | abcSameer      |
|  4 | Sai Kumar   | abcSai Kumar   |
|  5 | Naveen Sai  | abcNaveen Sai  |
|  6 | Seshi Kumar | abcSeshi Kumar |
|  7 | Rajiv Ram   | abcRajiv Ram   |
|  8 | NULL        | NULL           |
+----+-------------+----------------+

When you should use INSERT() Function in Snowflake?

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

  • If we want to insert expression or substring into a string, then we can make use of this function.

Real World Use Case Scenarios for INSERT Function in Snowflake.

  • If someone is interupting in a queue present in the hospital for check-up.
  • Furnituring an empty house.
  • Joining two metals by inserting a bolt and nut.

INSERT() Snowflake Official Documentation Link

Final Thoughts

In this article, we have learned about the INSERT() function and its 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