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