String into Column in Oracle

To do it first I will explain the use of INSTR() and and every thing will follow that

The instr function returns the location of a substring in a string.

INSTR('ARPITSINHA',SUB_STRING,Start Position, nth appearance)

Example

select INSTR('ARPITSINHA', 'A' , 1 , 1) from dual; it will return 1

select INSTR('ARPITSINHA', 'A',1) from dual; it will also return 1 because if you are not passing the start position the default is 1.

select INSTR('ARPITSINHA', 'A' , 1 , 2) from dual; it will return 10

Suppose if you have a string like 192.168.100.100 and you want it to break it into four separate strings then you can use this by finding the Dot (.) in between numbers.

SELECT SUBSTR(STR,1,INSTR(STR,'.',1,1)-1) "1ST" ,

SUBSTR(STR,INSTR(STR,'.',1,1) +1,(INSTR(STR,'.',INSTR(STR,'.',1,1),2)-1)-INSTR(STR,'.',INSTR(STR,'.',1,1),1)) "2ND",

SUBSTR(STR,INSTR(STR,'.',1,2) +1,(INSTR(STR,'.',INSTR(STR,'.',1,1),3)-1)-INSTR(STR,'.',INSTR(STR,'.',1,2),1)) "3RD",

SUBSTR(STR,INSTR(STR,'.',1,3) +1,LENGTH(STR)-INSTR(STR,'.',1,3)) "4TH"

FROM

(SELECT '192.168.100.100' STR FROM DUAL

);

1ST 2ND 3RD 4TH

--- --- --- ---

192 168 100 100

This query will find the dot in the IP address and will print the corresponding characters

Substr in Oracle

As per Oracle Documentation

The SUBSTR functions return a portion of string, beginning at a specified position in the string. The functions vary in how they calculate the length of the substring to return.

Note: When position is negative, then the function counts backward from the end of string.

SUBSTR(STRING, START_POSITION, NUMBER_OF_CHARACTER)

Example:

SQL> SELECT SUBSTR('ARPITSINHA',6,5) FROM DUAL;

SUBSTR

----------

SINHA