Options
Named value pair data in VARCHAR
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
0
Comments
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