stripping the strings in oracle

Question

i have below combinations as as input, Please help me with a function to return output variable as discussed below

COMPANY-ID_ABC_123          ==> This should give three outputs  (COMPANY-ID,ABC,123)
COMPANY-ID                  ==> this should give give three outputs such as (COMPANY-ID,null,null)
COMPANY-ID-FLAG             ==> this should give give three outputs such as (COMPANY-ID,FLAG,null)
COMPANY-ID-FLAG-ES      ==> this should give give three outputs such as (COMPANY-ID,FLAG,ES)
COMPANY-ID-ES               ==> this should give give three outputs such as (COMPANY-ID,ES,null)
IOB-2003                ==> if i give any other string other than the above , it has to just display (IOB-2003,null,null)

can you check if you have written all correctly? (i find two or three strange things... eg. COMPANY-ID-FLAG or COMPANY-ID_FLAG? but there are other too in description ) — etsa, May 05 '17 at 14:37
yeah COMPANY-ID is default, the first eg contains two other strings attached to it with underscore _. rest its all hyphen. — Anish Gopinath, May 05 '17 at 14:42
If it's so, pls revise and edit your question. Clarify if your are actual values too (eg. when you write COMPANY-ID you mean any value or just "COMPANY_ID"?) — etsa, May 05 '17 at 14:45
These are the inputs which i received from a java file, like COMPANY-ID, it just a string — Anish Gopinath, May 05 '17 at 14:47
Possible duplicate of [Is there a function to split a string in PL/SQL?](http://stackoverflow.com/questions/3710589/is-there-a-function-to-split-a-string-in-pl-sql) — Sudipta Mondal, May 05 '17 at 14:47
Are you sure `COMPANY-ID-FLAG-ES ` should be `COMPANY-ID,FLAG,null` ?? — Utsav, May 05 '17 at 14:48
also i have mentioned the desired output which i need on the Right part of each inputs — Anish Gopinath, May 05 '17 at 14:48
Utsav : Thanks for the correction, i have edited the question again — Anish Gopinath, May 05 '17 at 14:49
Before any programmatic solution can be built, you must be able to define rules that describe how to parse the strings. Edit your post (don't do it here in the comments) and list the rules. Show actual samples of data with combinations of the data elements you will actually have to work with. Without knowing what the string delimiters are, or what the element datatypes are, we can't define a workable solution. First do a little searching as how to parse strings has been asked and answered a zillion times. — Gary_W, May 05 '17 at 15:06
@gary, Whatever i have mentioned in the question is all i need, i have edited in my question and not on comments, there are many string parse available, but not with this combo, i have tried a few too but nothing worked for me. — Anish Gopinath, May 05 '17 at 15:13
@utsav : i need (test-foo-bar,null,null) or else just test-foo-bar return would do — Anish Gopinath, May 05 '17 at 15:14
thanks i can frame alittle answer based on the input from sudipta, — Anish Gopinath, May 05 '17 at 15:16
Please start your journey here, Grasshopper: http://stackoverflow.com/questions/31464275/split-comma-separated-values-to-columns-in-oracle/31464699#31464699. — Gary_W, May 05 '17 at 15:25

Chris Pratt · Answer 1 · 2017-05-06T21:32:18.100

Assuming those are the only in values to be considered, you could just hard code it with a multiple if-elsif-end statement. Otherwise you have to parse the input string. If I had to parse it I would first remove the hyphen from COMPANY-ID and then parse the string on the '-' and/or '_'.

CREATE OR REPLACE PROCEDURE getVal
    ( string_in    IN varchar2,
      string_out1 OUT varchar2,
      string_out2 OUT varchar2,
      string_out3 OUT varchar2 )
AS
   string_out1 := 'IOB-2003';
   string_out2 := 'null';
   string_out3 := 'null';

BEGIN

    IF string_in = 'COMPANY-ID_ABC_123' THEN
        string_out1 := 'COMPANY-ID';
        string_out2 := 'ABC';
        string_out3 := '123';

    ELSIF string_in = 'COMPANY-ID' THEN
        string_out1 := 'COMPANY-ID';

    ELSIF string_in = 'COMPANY-ID-FLAG' THEN
        string_out1 := 'COMPANY-ID';
        string_out2 := 'FLAG';

    ELSIF string_in = 'COMPANY-ID-FLAG-ES' THEN
        string_out1 := 'COMPANY-ID';
        string_out2 := 'FLAG';
        string_out3 := 'ES';

    ELSIF string_in = 'COMPANY-ID-ES' THEN
        string_out1 := 'COMPANY-ID';
        string_out2 := 'ES';

    END IF;

END;

stripping the strings in oracle

1 Answers1