Skip to main content

SUBSTR and INSTR Character Function in Oracle #DOTC_MDB

 

Character Function SUBSTR and INSTR in Oracle

 

Hello Everyone ,

Today we will discuss about character Function SUBSTR and INSTR in Oracle SQL and its uses.

In SQL Character Functions mainly operate on Character data , Character Function can be used with columns having character data.

 SUBSTR :  SUBSTR functions allows you to extract a substring from a string. SUBSTR function returns a string value . If length is negative then SUBSTR function return a null value.

Note:  If the position is positive, then Oracle Database counts from the beginning of char to find the first character. If the position is negative, then Oracle counts backward from the end of char

Example of SUBSTR Function in Oracle SQL.

To show uses of SUBSTR with different parameter I will take example from dual table and existing table in a database.

To get the first 3 character we need to write below SQL query by using SUBSTR function to get the result.

// GET FIRST 3 CHARACTER FROM DUAL TABLE

 SELECT SUBSTR('BANGALORE', 1, 3) AS LOCATION FROM dual;

Below is the output of above query .



// TO GET LAST 3 CHARACTER (NEGATIVE START POSITION)

 SELECT SUBSTR('BANGALORE', -3) FROM dual;

Below is the output of above Query .

 


// GET THE VALUE FROM 4TH BELOW IS THE QUERY

 SELECT SUBSTR('BANGALORE', 4) AS LOCATION FROM dual;

 

BELOW IS THE OUTPUT OF ABOVE QUERY.



 

IN SAME MANNER WE CAN USE SUBSTR WITH NEGATIVE AND POSITIVE NUMBER TO SEE THE RESULT AS PER OUR REQUIREMENT.

 SELECT SUBSTR('BANGALORE', -5,3) AS LOCATION FROM dual;

Above query will return ALO

SELECT SUBSTR('BANGALORE', -8,2) AS LOCATION FROM dual;

Above query will return AN .

You can check SUBSTR in a table and for particular column you can get the result in same manner.

//Using SUBSTR in a base table

 SELECT SUBSTR(name, -5,2) from employee where emp_id in(9876,2000);

Below is the output of above query .

 


//Using SUBSTR to see the last character we need to use below query

 

SELECT SUBSTR(name, -1) from employee where emp_id in(9876,2000);

Output of above query is as below:

 


Try to use SUBSTR as per your data in a table.

 

INSTR :  INSTR function In SQL  returns the location of a substring in a string. INSTR function returns a numeric value. The first position in the string is 1.If substring is not found in string, then the INSTR function will return 0.

Note : INSTR function searches for a substring in a string and returns the position of the substring in a string.

Example of INSTR Function in SQL.

// USING INSTR TO SEE THE FIRST OCCURRENCE OF A BY USING BELOW QUERY

SELECT INSTR('MY NAME IS ABCDE', 'A') FROM DUAL;

SELECT INSTR('MY NAME IS ABCDE', 'A', 1, 1) FROM DUAL;

BOTH QUERIES WILL GIVE THE POSITION OF THE FIRST OCCURENCE OF CHARACTER A IN STRING AS NUMBER.

Output of above query is as below.

 


// Second occurrence of A by using below query

SELECT INSTR('MY NAME IS ABCDE', 'A', 1, 2) from dual

Output of above query is as below

 


 

// Third occurrence of A by using below query

SELECT INSTR('MY NAME IS ABCDE AND ', 'A', 1, 3) FROM DUAL

  Output of above query is as below:

 


// SECOND OCCURENCE FROM BACK COUNTING AND IT WILL GIVE RESULT AS 12

 

SELECT INSTR('MY NAME IS ABCDE AND ', 'A', -3, 2) FROM DUAL

Output of above query is as below:


//Using INSTR  Function in a base table

SELECT ENAME, INSTR(ENAME,'A') as NAME_POS

FROM EMP

Below is the output of above query.

 


Please go through this blog and practice Character Function SUBSTR and INSTR as per your requirement and for any questions please let me know.  In next blog I will come up with more character functions like TRIM,Length,LPAD,RPAD,TRIM etc.

 

Comments

Post a Comment

Popular posts from this blog

SQL and Classification of SQL in Oracle Database #Diksha Oracle Training Centre

  SQL and Classification of SQL in Oracle Database.   SQL is Structured Query Language , which is used for storing, manipulating and retrieving data stored in a relational database .SQL is the standard language for RDBMS. All the Relational Database Management Systems (RDMS) like Oracle, MySQL, Sybase, Informix, IBM DB2 and Microsoft SQL Server use SQL as their standard database language. Oracle is one of the more secured database as compared to other databases. Importance of   SQL : SQL and PL/SQL is a backend process where all data is stored and retrieved in GUI which created either by any programming languages like Java, C++, PHP etc. so we need to have very secure database so that there will be no impact for users. SQL allows users to access data in the relational database management systems. SQL is used to communicate with a database.SQL and PL/SQL allows users to create and drop databases tables , views , stored procedures , functions , packages , trigger etc. SQL allows

Materialized View uses and Benefits in Database #DOTC_Mdb

Materialized View uses and Benefits in Database. Hello Everyone, Today we will discuss about Materialized view as it’s play important role in database. We already discussed about Simple Views and complex views in my previous blog. Before Materialized view go through my previous blog which related to simple view. https://dheeraj60.blogspot.com/2020/05/benefits-of-creating-and-using-view-in.html As we know View is not a database object and not like table which is stored in database, but view can be created from base table a view is an SQL statement that’s stored in the database. This statement, or view, has a name.A view looks and acts a lot like a table. It has columns and rows, and can be included in SELECT queries just like a table. In other word we can say View is a virtual/logical table which is basically used for security purpose. Let’s understand   about   Materialized view : A materialized view is a view that stores the results of the view’s query. Whenever you query the ma

Top 50 Interview Questions On SQL and PL/SQL #DOTC_Mdb

                    Top 50 Interview Questions On SQL and PL/SQL. Today we will Discuss Top 50 interview questions and answers of SQL and PL/SQL which is frequently asked in interview.     Question 1: What is SQL and Classification of SQL? Answer SQL is a Structure Query Language which is vastly used in RDBMS database like Oracle, Sybase, DB2 , Microsoft SQL server etc.   Classification of SQL is as below: DDL (Data Definition Language):  Commands are  create , alter , drop , truncate etc DML (Data Manipulation Language) : Commands are  insert,update and delete . TCL (Transaction Control Language ) : Commands are  Commit , Rollback and Save point. DCL (Data Control Language) : Commands are Grant , Revoke Question 2:    What is meant by Joins? What are the types of join? Answer Joins are basically used to extract/get data from multiple tables using some common columns or conditions and also by using alias to fulfill the condition.   There are various types of Joins as li