PL/SQL key-value String using Regex

Question

I have a String stored in a table in the following key-value format: "Key1☺Value1☺Key2☺Value2☺KeyN☺ValueN☺". Given a Key how can I extract the Value? Is regex the easiest way to handle this? I am new to PL/SQL as well as Regex.

Answer 1

In this case, I would use just a regular split and iterate through the resulting array.

public string GetValue(string keyValuePairedInput, string key, char separator)
{
    var split = keyValuePairedInput.Split(separator);

    if(split.Lenght % 2 == 1)
        throw new KeyWithoutValueException();

    for(int i = 0; i < split.Lenght; i += 2)
    {
        if(split[i] == key)
            return split[i + 1];
    }

    throw new KeyNotFoundException();
}

(this was not compiled and is not pl/sql anyway, treat it as pseudocode ☺)

OK I hear your comment... Making use of pl/sql functions, you might be able to use something like this:

select 'key' as keyValue,
(instr(keyValueStringField, keyValue) + length(keyValue) + 1) as valueIndex,
substr(keyValueStringField, valueIndex,  instr(keyValueStringField, '\1', valueIndex) - valueIndex) as value
from Table

Answer 2

For this kind of string slicing and dicing in PL/SQL you will probably have to use regular expressions. Oracle has a number of regular expression functions you can use. The most commonly used one is REGEXP_LIKE which is very similar to the LIKE operator but does RegEx matching.

However you probably need to use REGEXP_INSTR to find the positions where the separators are then use the SUBSTR function to slice up the string at the matched positions. You could also consider using REGEXP_SUBSTR which does the RegEx matching and slicing in one step.

Answer 3

As an alternative to regular expressions...

Assuming you have an input such as this: Key1,Value1|Key2,Value2|Key3,Value3

You could use some PL/SQL as shown below:

   FUNCTION get_value_by_key
   (
      p_str           VARCHAR2
    , p_key           VARCHAR2
    , p_kvp_separator VARCHAR2
    , p_kv_separator  VARCHAR2
   ) RETURN VARCHAR2
    AS
      v_key   VARCHAR2(32767);
      v_value VARCHAR2(32767);
      v_which NUMBER;      
      v_cur   VARCHAR(1);
    BEGIN
      v_which := 0;
      FOR i IN 1..length(p_str)
      LOOP
         v_cur := substr(p_str,i,1);         
         IF v_cur = p_kvp_separator
         THEN
            IF v_key = p_key
            THEN
               EXIT;
            END IF;
            v_key := '';
            v_value := '';
            v_which := 0;
         ELSIF v_cur = p_kv_separator
         THEN
            v_which := 1;
         ELSE
            IF v_which = 0
            THEN
               v_key := v_key || v_cur;
            ELSE
               v_value := v_value || v_cur;
            END IF;
         END IF;
      END LOOP;

      IF v_key = p_key
      THEN
         RETURN v_value;
      END IF;
      raise_application_error(-20001, 'key not found!');      
   END;

To get the value for 'Key2' you could do this (assuming your function was in a package called test_pkg):

SELECT test_pkg.get_value_by_key('Key1,Value1|Key2,Value2|Key3,Value3','Key2','|',',') FROM dual