Replace multiple tags in a row in select statement from another table

Question

I have two tables.
One is answer_step_dtl
Another is tag_mst as follow

Ans_code                      Ans_Desc
-------------------------------------------
50000000000000005770       Enter <B><APN></B> and press Ok.       
40000000000000000164       Enter <B><ACCOUNTNAME></B> in connection name.
40000000000000000165       Enter <B><ACCOUNTNAME></B> in <APN>.

and so on.

And my tag_mst has values like this

TAG_CODE                    TAG_NAME          TAG_VALUE
-------------------------------------------------------
100                          <APN>            EXAMPLE.COM
101                          <ACCOUNTNAME>    EXAMPLE

Now my requirement is this I want to select answer form answer table with replaced value of tag mast. There can be more than one tag in the one ans_desc.

I was thinking the approach

Loop through the result of answer_dtl fetch the tags from individual record
loop through the multiple tags in the record and then replace the tag
store in a temporary table and then open the cursor for new tem table.

Is this is the approach. Or is there any easy approach of doing this.

Is it possible that you have two tag names in the same ans_desc? — Ben
– Ben, Commented Nov 9, 2012 at 8:38
yes this is the possible otherwise the @joe solution is perfect. — शेखर
– शेखर, Commented Nov 9, 2012 at 8:47
@NicholasKrasnov I have other column in the TAG_MST on the basis of which I filter the values from tag_mst. There is many to one mapping between these two table. — शेखर
– शेखर, Commented Nov 9, 2012 at 9:18
@krshekhar In the case there are several tags in TAG_NAME then I suppose there should be a corresponding tag values or other tags are alternatives? — Nick Krasnov
– Nick Krasnov, Commented Nov 9, 2012 at 9:18

Community · Accepted Answer · 2017-05-23 12:04:35Z

3

Edit: Bonus query using (recursive) CTE, inspired by a note from @Rob van Wijk, needs 11g(R2?):

SQL> WITH data AS (
  2     SELECT ans_code, Ans_Desc, tag_name, tag_value,
  3            row_number() OVER (partition BY ans_code ORDER BY t.rowid) no,
  4            row_number() OVER
  5               (partition BY ans_code ORDER BY t.rowid DESC) is_last
  6        FROM answer_step_dtl a
  7        JOIN tag_mst t ON a.ans_desc LIKE '%' || t.tag_name || '%'
  8  ), n(ans_code, no, is_last, replaced) AS (
  9     SELECT ans_code, no n, is_last,
 10            replace (ans_desc, tag_name, tag_value) replaced
 11       FROM data
 12      WHERE no = 1
 13     UNION ALL
 14     SELECT d.ans_code, d.no, d.is_last,
 15            replace (n.replaced, d.tag_name, d.tag_value) replaced
 16       FROM data d
 17       JOIN n ON d.ans_code = n.ans_code
 18        AND d.no = n.no + 1
 19  )
 20  SELECT *
 21    FROM n
 22   WHERE is_last=1;

ANS_CODE             NO IS_LAST REPLACED
-------------------- -- ------- ---------------------------------------
40000000000000000164  1       1 Enter <B>EXAMPLE</B> in connection
50000000000000005770  1       1 Enter <B>EXAMPLE.COM</B> and press Ok.
40000000000000000165  2       1 Enter <B>EXAMPLE</B> in EXAMPLE.COM.

Initial answer:

You can use of a PL/SQL function. The following will work even if there are several tags to be replaced:

CREATE OR REPLACE FUNCTION replacetags(p_desc VARCHAR2)
   RETURN VARCHAR2 IS
   l_result  LONG := p_desc;
   l_tag_pos INTEGER := 1;
   l_tag     tag_mst.tag_name%TYPE;
BEGIN
   LOOP
      l_tag     := regexp_substr(l_result, '<[^<]+>', l_tag_pos);
      l_tag_pos := regexp_instr(l_result, '<[^<]+>', l_tag_pos) + 1;
      EXIT WHEN l_tag IS NULL;
      BEGIN
         SELECT replace(l_result, l_tag, tag_value)
           INTO l_result
           FROM tag_mst
          WHERE tag_name = l_tag;
      EXCEPTION
         WHEN no_data_found THEN
            NULL; -- tag doesn't exist in tag_mst
      END;
   END LOOP;
   RETURN l_result;
END;

SQL> SELECT ans_code, replacetags(ans_desc)
  2    FROM answer_step_dtl;

ANS_CODE              REPLACETAGS(ANS_DESC)
--------------------- ----------------------------------------
50000000000000005770  Enter <B>EXAMPLE.COM</B> and press Ok.
40000000000000000164  Enter <B>EXAMPLE</B> in connection
40000000000000000165  Enter <B>EXAMPLE</B> in EXAMPLE.COM.

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Nov 9, 2012 at 9:30

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

शेखर Over a year ago

thaks @Vincent I am going to apply this and see it

शेखर Over a year ago

thanks @Vincent it is working perfectly. But will their be some overhead (open cursor issue) if I answer_step_dtl has 60 to 70 step and each step has some tag.

Vincent Malgrat Over a year ago

This will read rows from answer_step_dtl only once, and for each tag you will get an index unique scan so this should be OK. There will be no open cursor issue since the function doesn't use explicit cursors :) If you're on 11gR2 you can try the other query and tell us which is fastest on your dataset.

Joe G Joseph · Accepted Answer · 2012-11-09 07:43:33Z

1

try this:

select d."Ans_code",replace("Ans_Desc","TAG_NAME","TAG_VALUE")
from   answer_step_dtl d, tag_mst m
where "Ans_Desc" like '%'|| "TAG_NAME" || '%'

SQL fiddle demo

answered Nov 9, 2012 at 7:43

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

2 Comments

शेखर Over a year ago

I have tried by editing but for multiple tag it is not working.

Joe G Joseph Over a year ago

@krshekhar: then try updating the table instead of selecting

Collectives™ on Stack Overflow

Replace multiple tags in a row in select statement from another table

2 Answers 2

3 Comments

SQL fiddle demo

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related