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 - 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

Fetch Substring in Oracle

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

​x
 
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;

 
-- 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.

Answer

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>

 
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!!

Advertisement

Answer