INSTR Question 1

[Abslag]

Hello All,
I am trying to do an INSTR (unless someone has a better suggestion) The reference field combines several values that I need to isolate


SELECT MS.IDENTITY,
MS.INVOICE_NO,
MS.CODE_A,
MS.REFERENCE
FROM IFSAPP.MAN_SUPP_INVOICE_POSTINGS MS
WHERE MS.CODE_A IN ('5050','5051')

RESULTS
IDENTITY INVOICE_NO CODE_A REFERENCE
1129E 153347 5051 100230431^1^1^1^
1129E 153347 5051 100230431^13^1^1^
1129E 153347 5051 100230431^28^1^1^
1129E 153347 5051 100230431^47^1^1^
1129E 153347 5051 100230431^52^1^1^
1129E 153347 5051 100230431^54^1^1^
1129E 153347 5051 100230431^57^1^1^
1129E 153347 5051 100230431^63^1^1^


If you see the reference field each of the values need to be isolated between the '^'

Order_No, Line_No, Release_No, Receipt_No

I could figure out how to get the Order_No out with a substr but the field lengths will vary, sometimes the Order_no might be 4 char or as above, 9.

Any suggestions?

 

[sem]

Do you need a suugestion that differs from INSTR/SUBSTR combination?

Regards, Dima

http://seminihin.narod.ru

 

[Abslag]

Right now I am trying to find the simplest way whether it is INSTR/SUBSTR or something else. I am open for any suggestions.

Thanks in advance

 

[SantaMufasa]

WBSlag,

Here is "hardcoded" code to do what you want (followed by an example of a much-improved, simplified method that relies upon a user-defined "Unstring" function):

col a heading "IDENTITY" format a20
col b heading "INVOICE_NO" format 999999
col c heading "CODE_A" format   9999
col d heading "ORDER_NO" format a10
col e heading "LINE_NO" format  a7
col f heading "RELEASE_NO" format a10
col g heading "RECEIPT_NO" format a10
col h heading "REFERENCE" format  a20
SELECT  IDENTITY    a
       ,INVOICE_NO  b
       ,CODE_A      c
       ,substr(reference,1,instr(reference,'^')-1) d 
       ,substr(reference
            ,instr(reference,'^',1,1)+1
            ,(instr(reference,'^',1,2)-instr(reference,'^',1,1))-1) e
       ,substr(reference
            ,instr(reference,'^',1,2)+1
            ,(instr(reference,'^',1,3)-instr(reference,'^',1,2))-1) f
       ,substr(reference
            ,instr(reference,'^',1,3)+1
            ,(instr(reference,'^',1,4)-instr(reference,'^',1,3))-1) g
       ,REFERENCE h
FROM IFSAPP.MAN_SUPP_INVOICE_POSTINGS
WHERE CODE_A IN ('5050','5051');

IDENTITY             INVOICE_NO CODE_A ORDER_NO   LINE_NO RELEASE_NO RECEIPT_NO REFERENCE
-------------------- ---------- ------ ---------- ------- ---------- ---------- --------------------
1129E                    153347   5051 100230431  1       1          2          100230431^1^1^2^
1129E                    153347   5051 100230431  13      3          4          100230431^13^3^4^
1129E                    153347   5051 100230431  28      5          6          100230431^28^5^6^
1129E                    153347   5051 100230431  47      7          8          100230431^47^7^8^
1129E                    153347   5051 100230431  52      9          10         100230431^52^9^10^
1129E                    153347   5051 100230431  54      11         12         100230431^54^11^12^
1129E                    153347   5051 100230431  57      13         14         100230431^57^13^14^
1129E                    153347   5051 100230431  63      15         16         100230431^63^15^16^

8 rows selected.
****************************************************************************************************

Here is a user-defined function that unstrings any incoming string, with any invoker-defined delimiter ("^" in your case), provided that each substring within an incoming string ends with that same invoker-defined delimiter:

create or replace function unstring
    (str_in varchar2
    ,delim varchar2
    ,which number)
    RETURN varchar2
is
begin
    if which = 1 then
        return substr(str_in,1,instr(str_in,delim)-1);
    else
        return substr(str_in
            ,instr(str_in,delim,1,which-1)+1
            ,(instr(str_in,delim,1,which)-instr(str_in,delim,1,which-1))-1);
    end if;
end;
/

Function created.

Now, notice how much "cleaner" and error resistant your query becomes with the "unstring" function:

SELECT  IDENTITY    a
       ,INVOICE_NO  b
       ,CODE_A      c
       ,unstring(reference,'^',1) d
       ,unstring(reference,'^',2) e
       ,unstring(reference,'^',3) f
       ,unstring(reference,'^',4) g
       ,REFERENCE h
FROM IFSAPP.MAN_SUPP_INVOICE_POSTINGS
WHERE CODE_A IN ('5050','5051');

IDENTITY             INVOICE_NO CODE_A ORDER_NO   LINE_NO RELEASE_NO RECEIPT_NO REFERENCE
-------------------- ---------- ------ ---------- ------- ---------- ---------- --------------------
1129E                    153347   5051 100230431  1       1          2          100230431^1^1^2^
1129E                    153347   5051 100230431  13      3          4          100230431^13^3^4^
1129E                    153347   5051 100230431  28      5          6          100230431^28^5^6^
1129E                    153347   5051 100230431  47      7          8          100230431^47^7^8^
1129E                    153347   5051 100230431  52      9          10         100230431^52^9^10^
1129E                    153347   5051 100230431  54      11         12         100230431^54^11^12^
1129E                    153347   5051 100230431  57      13         14         100230431^57^13^14^
1129E                    153347   5051 100230431  63      15         16         100230431^63^15^16^

8 rows selected.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Abslag]

Santa,
I think for what I am doing the hardcoded version will be perfect (even though it is longer). Thank you so much.
You have a gift-[moroni 10]

 

[SantaMufasa]

Thanks for your kind words and hidden message. . Reach me via my signature for a non-technical conversation.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Beilstwh]

just a thought. Why are you using a single column to contain multiple columns of information. If the syntax of the reference column is always the same, I would add the Order_No, Line_No, Release_No, Receipt_No columns to the table and store the information in the individual columns. This would allow you a lot more flexability and simplify your code.

Bill
Oracle DBA/Developer
New York State, USA

 

[Abslag]

Beilstwh,
A valid point. In my position I have all the permission I need to retrieve data from the tables. Just none that would permit me to put data in the tables so I am not able to create the data.

Thanks Anyway