Re: Smart SQL function wanted

It is fairly easy ... the output is comma delimited ...

CREATE OR REPLACE FUNCTION raj_test (p_in VARCHAR2) RETURN VARCHAR2 IS

--
szResult  VARCHAR2(200);
bPos      NUMBER;
epos      NUMBER;
p_text    VARCHAR2(2000) := p_in;
--
BEGIN
  --
  szResult := NULL;
  LOOP
    bpos := 0;
    epos := 0;
    --
    bPos := INSTR(p_text,'-',1, 1);
    IF bPos = 0 THEN
      EXIT;
    END IF;
    --
    epos := INSTR(p_text,'-',1, 2);
    --
    szResult := szResult || ',' || SUBSTR(p_text, bpos+1, (epos-bpos-1));
    p_text := SUBSTR(p_text, epos+1);
    --
  END LOOP;
  --
  RETURN trim(BOTH ',' FROM szResult);
  --
END raj_test;
/

SELECT duh, raj_test(duh)
FROM(
SELECT '276/S-BSFPO-159/N' duh FROM dual
UNION ALL
SELECT '154/NE-PAMAN-061/E-BSFPO-276/S' duh FROM dual
UNION ALL
SELECT '276/N-BSFPO-159/NE-USMIA-770/W1-PAMAN-792/U' duh FROM dual)
/

HTH

Raj

"Christian Svensson" <chse30_at_hotmail.com> wrote in message
news:ccc2a7eb.0312220517.5e896a5c_at_posting.google.com...


> Greetings all,

>

> I wonder if any Oracle SQL gurus out there have any smart ways of

> solving this string problem.

>

> We have in a column this kind of example data:

>

> 276/S-BSFPO-159/N

> 154/NE-PAMAN-061/E-BSFPO-276/S

> 276/N-BSFPO-159/NE-USMIA-770/W1-PAMAN-792/U

>

> I want to find the strings that are between the "-" characters. For

> example in the string 276/N-BSFPO-159/N I want to get BSFPO. In the

> string 276/N-BSFPO-159/N-USMIA-770/W-PAMAN-792/U I want to get BSFPO

> and USMIA and PAMAN.

>

> Then I want to use these codes to be used in a WHERE statement, for

> example:

>

> WHERE RKST_CODE IN ('BSFPO','USMIA','PAMAN')

>

> As you can see, sometimes there will be just one code, and sometimes

> there will be 3 codes to find (up to 7 codes max).

>

> I can not use substrings with fixed position, so my current solution

> is to use instr to get positions of "-" character and then use that as

> an input to substr. But it will be a verry messy/hard-to-understand

> code.

>

> Does anyone out there have any better smart ideas of how to solve this

> ?

>

> Any url:s would be great.

>

> Thanks and merry Christmas !

>

> /Christian