Named value pair data in VARCHAR

Combination of instr and substr funtions can solve your request

Thank you Sir, could you please post an example.

You could also switch the format of the data to be json, and then store it as a map. See the coljson example:

  https://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/FlexTables/MAPJSONEXTRACTOR.htm?Highlight=coljson

  --Sharon

You can use a SQL Function and regular expression:

create or replace function getattr(str varchar, attr varchar) return varchar as

begin

  return regexp_substr(str, attr || '\+(\w+)', 1, 1, '', 1);

end

select getattr('Sequence+19;Description+SB;OrderStatus+Created', 'Sequence');

19

select getattr('Sequence+19;Description+SB;OrderStatus+Created', 'Description');

SB

select getattr('Sequence+19;Description+SB;OrderStatus+Created', 'OrderStatus');

Created

select getattr('Sequence+19;Description+SB;OrderStatus+Created', 'Foo');

[NULL]

These examples assume that Standard Conforming Strings is on (default).

  --Sharon

Hi

The most simples approach is to translate it to a Json format ( as Sharon already mention ) and then  used both maplookup() and mapjsonextractor() functions .

Below is an example ( the last parameter is the name of the key which you like to get is value) :

dbadmin=> Select maplookup(mapjsonextractor('{"Sequence":"19", "Description":"SB", "orderStatus":"Created"}'),'Description');
maplookup
-----------
SB
(1 row)

dbadmin=> Select maplookup(mapjsonextractor('{"Sequence":"19", "Description":"SB", "orderStatus":"Created"}'),'orderStatus');
maplookup
-----------
Created
(1 row)

I hope you will find it helpful

Thanks 

Thank you Sharan and Eli for your valuable inputs.

Keep in mind that using regular expressions (and other substring expressions) is cpu-intense.  Hopefully you will only be executing these queries on relatively small data sets.  If you were to execute this on billions and billions of rows, it's likely to peg all of your CPUs unless you plan for it with your resource pool configurations.  Whether this is a problem will depend on your use case.

  --Sharon