oracle regular expression or internal function usage

Question

I have a oracle table with a column that has data as below

CREATE TABLE AB
( id varchar2(10),
  URL varchar2(4000)); 

INSERT INTO AB (id, url)
         VALUES (
                    1,
                    "https://www.moveloot.com/sf?utm_source=linkedin&utm_medium=paid&utm_content=bamboo&utm_campaign=San_Francisco_web_SF_SignUps_LAL_2_1839391141&utm_term=abc-123&utm_content=linked_in_ad-1");

COMMIT;

what is the best approach in oracle sql to extract the values below from the url

utm_source = linkedin
utm_medium = paid
utm_content = bamboo
utm_campaign = San_Francisco_web_SF_SignUps_LAL_2_1839391141
utm_content = linked_in_ad-1

score 1 · Accepted Answer · answered Sep 16 '19 at 15:57

So first a direct answer to the question:

Oracle directly supports extracting a substring matching a regular expression (https://docs.oracle.com/cd/B19306_01/B14251_01/adfns_regexp.htm), so I would do something like this

SELECT REGEX_REPLACE(
    REGEX_SUBSTR(ab.URL, 'utm_source=[^&]*'),
    'utm_source=',
    '') as UTM_SOURCE,--repeat for each sub-field
FROM AB ab
WHERE ab.id = 1;

This will work... but its going to result in some truly awful SQL. Not only that, but if you ever have to migrate away from Oracle (which my company is doing), this will probably have to be rewritten.

Instead, if the option is available, I would definitely do the string parsing on the front end. In code, you can do something like:

Map<String, String> urlParams = new HashMap<String, String>();
if (url.contains("?"))
{
  List<String> paramClauses = url.split("?")[1];
  for(String clause : paramClauses)
  {
    urlParams.put(clause.split("=")[0], clause.split("=")[1]);
  }
}
return urlParams;

Alternately, you might need to do actual relational logic with the params. If that is the case, I would parse them out into separate columns (so one for each of utm_source, utm_medium, utm_content, etc). This moves over into a data architecture question that's out of scope for this venue... but if what you actually need from this data is the url params, this gets super close to the discussion here Is storing a delimited list in a database column really that bad?

oracle regular expression or internal function usage

1 Answers1