SubStr and InStr 1

[jasonhuibers]

SELECT
SUBSTR('\NAME\ADDRESS\CITY\STATE' , INSTR('\NAME\ADDRESS\CITY\STATE','\', 1, 2)-5 ) ADDRESS,
SUBSTR('\NAME\ADDRESS\CITY\STATE' , INSTR('\NAME\ADDRESS\CITY\STATE','\', 1, 2)-5 ) CITY
FROM dual;

How can I change this query to return ADDRESS and CITY in seperate columns as the result?

COL1: ADDRESS
COL2: CITY

 

[carp]

Try something like the following:

SELECT 
SUBSTR('\NAME\ADDRESS\CITY\STATE', INSTR('\NAME\ADDRESS\CITY\STATE','\', 1, 2)+1,
        INSTR('\NAME\ADDRESS\CITY\STATE','\', 1, 3)- INSTR('\NAME\ADDRESS\CITY\STATE','\', 1, 2)-1) ADDRESS, 
SUBSTR('\NAME\ADDRESS\CITY\STATE', INSTR('\NAME\ADDRESS\CITY\STATE','\', 1, 3)+1,
        INSTR('\NAME\ADDRESS\CITY\STATE','\', 1, 4)- INSTR('\NAME\ADDRESS\CITY\STATE','\', 1, 3)-1)  CITY
FROM dual;