CODEWITHSUNDEEP
sqlregexoracle-database
Ankit Bajpai
Ankit Bajpai

Reputation: 13509

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

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.

Upvotes: 0

Views: 71

Answers (1)

Popeye
Popeye

Reputation: 35900

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

Upvotes: 3

Related Questions