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.

I'm seeing "smiley faces" in your key-value format. What character is that? — dcp
– dcp, Commented Nov 19, 2009 at 20:21
Nope its a char, '\1' and it is used between the key, value, and pairs. — Rodney
– Rodney, Commented Nov 19, 2009 at 20:58
Usually you have a separate delimiter for separating keys and values, and their pairs. But if the character you are using for the delimiter is the same as a character that can occur within a key or value, then you have a problem. The code I posted below assumes the 2 delimiters won't occur in the input, and that the 2 delimiters differ. — dcp
– dcp, Commented Nov 19, 2009 at 21:11

Joaquim Rendeiro · Accepted Answer · 2009-11-19 20:51:40Z

0

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

edited Nov 19, 2009 at 20:51

answered Nov 19, 2009 at 20:31

Joaquim Rendeiro

1,3988 silver badges13 bronze badges

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

1 Comment

Rodney Over a year ago

this looks like java, not pl/sql.

Tendayi Mawushe · Accepted Answer · 2009-11-19 20:52:03Z

0

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.

answered Nov 19, 2009 at 20:52

Tendayi Mawushe

26.2k7 gold badges53 silver badges57 bronze badges

Comments

dcp · Accepted Answer · 2009-11-19 20:54:25Z

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

Collectives™ on Stack Overflow

PL/SQL key-value String using Regex

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related