Substr and INstr

[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

 

[yelworcm]

This looks to be Oracle, rather than SQL Server. But.....

What does this give you?

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, 3)-5 ) CITY
FROM dual;

INSTR has a nice parameter "occurance", which is documented here:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions080.htm#i77598

 

[jasonhuibers]

This gives me:

Address: City:
\NAME\ADDRESS\CITY\STATE DRESS\CITY\STATE

 

[jasonhuibers]

This gives me:

Address column: \NAME\ADDRESS\CITY\STATE
City column: DRESS\CITY\STATE

 

[SQLBill]

I'd suggest posting this in the Oracle forum. While some of the posters on this forum (Microsoft SQL Server: Programming) might know Oracle and be able to help, you'll get your best help from the Oracle thread.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875