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

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

     

  • 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
  • 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

     

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file