Results 1 to 3 of 3

Thread: pl/sql strings

  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Unanswered: pl/sql strings

    I have a simple query:
    Select A, NVL(C1,0), NVL(decode(C2,'YES','GOOD','BAD'),'UNKNOWN')
    FROM T1;

    I wan't a function which accepts the above query as input and returns the following query (string NOT the execution result) as output:

    Select A || NVL(C1,0) || NVL(decode(C2,'YES','GOOD','BAD'),'UNKNOWN')
    FROM T1;

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    use the INSTR function to search for the commas, and nest it in a SUBSTR function.
    For example, SUBSTR(a,instr(a,',',1,1),instr(a,',',1,2)-1)).
    Put this in a loop for i to n, where n is the number of commas in your sql statement.
    For example:
    FOR i in 1 ... n LOOP
    v_output := v_output || '||' || SUBSTR(a,instr(a,',',1,1),instr(a,',',1,2)-1));
    END LOOP;

    Note the || within single quotes are literals, and the || not in quotes are concat functions.

    -Mark

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    also beware of quotation marks in the returned string.
    Might take some testing to get all of them correct.
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •