Fetch Substring in Oracle

Question

I have a string as -

V_TAG_B = utm_source=google_search&utm_medium=cpc&utm_term={Keyword}&utm_campaign=home-|-SBI-|-search

I need to break this string into 4 small parts as -

V_UTM_SOURCE = utm_source=google_search&
V_UTM_MEDIUM = utm_medium=cpc&
V_UTM_TERM = utm_term={Keyword}&
V_UTM_CAMPAIGN = utm_campaign=home-|-SBI-|-search

I need to do this because the string can be in any order such as utm_campaign coming first and utm_source is coming at last. So after breaking it into pieces i will concat it again and will match with our DB table in which a column have the same value as this string. I have achieved this using SUBSTR/INSTR combination as below -

-- Assigning First Keyword
      IF UPPER(SUBSTR(V_TAG_B, 1, INSTR(V_TAG_B, '=', 1, 1)-1)) LIKE '%UTM_SOURCE%' THEN
         V_UTM_SOURCE := SUBSTR(V_TAG_B, 1, INSTR(V_TAG_B, '&', 1, 1));
      ELSIF UPPER(SUBSTR(V_TAG_B, 1, INSTR(V_TAG_B, '=', 1, 1)-1)) LIKE '%UTM_MEDIUM%' THEN
         V_UTM_MEDIUM := SUBSTR(V_TAG_B, 1, INSTR(V_TAG_B, '&', 1, 1));
      ELSIF UPPER(SUBSTR(V_TAG_B, 1, INSTR(V_TAG_B, '=', 1, 1)-1)) LIKE '%UTM_TERM%' THEN
         V_UTM_TERM := SUBSTR(V_TAG_B, 1, INSTR(V_TAG_B, '&', 1, 1));
      ELSE
         V_UTM_CAMPAIGN := SUBSTR(V_TAG_B, 1, INSTR(V_TAG_B, '&', 1, 1));
      END IF;

      -- Assigning Second Keyword
      IF UPPER(SUBSTR(V_TAG_B, INSTR(V_TAG_B, '&', 1, 1)+1)) LIKE '%UTM_SOURCE%' THEN
         V_UTM_SOURCE := SUBSTR(SUBSTR(V_TAG_B, INSTR(V_TAG_B, '&', 1, 1)+1), 1, INSTR(SUBSTR(V_TAG_B, INSTR(V_TAG_B, '&', 1, 1)+1), '&', 1, 1));
      ELSIF UPPER(SUBSTR(V_TAG_B, INSTR(V_TAG_B, '&', 1, 1)+1)) LIKE '%UTM_MEDIUM%' THEN
         V_UTM_MEDIUM := SUBSTR(SUBSTR(V_TAG_B, INSTR(V_TAG_B, '&', 1, 1)+1), 1, INSTR(SUBSTR(V_TAG_B, INSTR(V_TAG_B, '&', 1, 1)+1), '&', 1, 1));
      ELSIF UPPER(SUBSTR(V_TAG_B, INSTR(V_TAG_B, '&', 1, 1)+1)) LIKE '%UTM_TERM%' THEN
         V_UTM_TERM := SUBSTR(SUBSTR(V_TAG_B, INSTR(V_TAG_B, '&', 1, 1)+1), 1, INSTR(SUBSTR(V_TAG_B, INSTR(V_TAG_B, '&', 1, 1)+1), '&', 1, 1));
      ELSE
         V_UTM_CAMPAIGN := SUBSTR(SUBSTR(V_TAG_B, INSTR(V_TAG_B, '&', 1, 1)+1), 1, INSTR(SUBSTR(V_TAG_B, INSTR(V_TAG_B, '&', 1, 1)+1), '&', 1, 1));
      END IF;

I guess this can be shortly and easily achieved using REGEXP SUBSTR also. Any help/suggestion is appreciated.

Popeye · Accepted Answer · 2020-01-24 07:11:54Z

3

You can use REGEXP_SUBSTR as following:

SQL> SELECT
  2      REGEXP_SUBSTR(STR, 'utm_source=[^&]+') as V_UTM_SOURCE,
  3      REGEXP_SUBSTR(STR, 'utm_medium=[^&]+') as V_UTM_MEDIUM,
  4      REGEXP_SUBSTR(STR, 'utm_term=[^&]+') as V_UTM_TERM,
  5      REGEXP_SUBSTR(STR, 'utm_campaign=[^&]+') as V_UTM_CAMPAIGN
  6  FROM
  7      ( SELECT 'V_TAG_B = utm_source=google_search&utm_medium=cpc&utm_term {Keyword}&utm_campaign=home-|-SBI-|-search' AS STR
  8          FROM DUAL);

V_UTM_SOURCE              V_UTM_MEDIUM         V_UTM_TERM           V_UTM_CAMPAIGN
------------------------- -------------------- -------------------- ---------------------------------
utm_source=google_search  utm_medium=cpc       utm_term={Keyword}   utm_campaign=home-|-SBI-|-search

SQL>

Cheers!!

answered Jan 24, 2020 at 7:11

Popeye

36k4 gold badges12 silver badges31 bronze badges

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

3 Comments

Ankit Bajpai Over a year ago

2 questions. 1. What if i want to append& also with the values so i dont need to append them explicitely later. 2. why didn't you get any prompt to enter values for utm_medium due to & character in string?

Popeye Over a year ago

For 1.

SELECT     REGEXP_SUBSTR(STR, 'utm_source=[^&]+') as V_UTM_SOURCE,     REGEXP_SUBSTR(STR, 'utm_medium=[^&]+&') as V_UTM_MEDIUM,     REGEXP_SUBSTR(STR, 'utm_term=[^&]+&') as V_UTM_TERM,     REGEXP_SUBSTR(STR, 'utm_campaign=[^&]+') as V_UTM_CAMPAIGN FROM     ( SELECT 'V_TAG_B = utm_source=google_search&utm_medium=cpc&utm_term={Keyword}&utm_campaign=home-|-SBI-|-search' AS STR         FROM DUAL);

----------- and 2. SET DEFINE OFF

Ankit Bajpai Over a year ago

Thank you so much for the answer. I just realised i cannot use & with the parts anyways coz i cannot determine what is last part in the string. So I am going with original answer.

Collectives™ on Stack Overflow

Fetch Substring in Oracle

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related