Named value pair data in VARCHAR

ic10134

Hi All,

We are trying to read LONG VAR CHAR data which has named value pair separated by '+' and the columns itself are delimited by semicolon.

For example the column has data like this:

 

Sequence+19;Description+SB;OrderStatus+Created

If we pass attribute name as Sequence, then it should return: 19
If we pass attribute name as Description, then it should return: SB
If we pass attribute name as OrderStatus, then it should return: Created.

Please advise if there is any way to achieve this

FiliN

Combination of instr and substr funtions can solve your request

ic10134

Thank you Sir, could you please post an example.

Sharon_Cutter

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

 

ic10134

Thank you Sharon. But we are storing the native data as is. Is there a way to use any vertica sql functions in select clause to achieve this. Please advise

Sharon_Cutter

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

 

eli_revach

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 

 

ic10134

Thank you Sharan and Eli for your valuable inputs.

Sharon_Cutter

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