MAPDELIMITEDEXTRACTOR

Extracts data with a delimiter character, and other optional arguments, returning a single VMap value. The USING PARAMETERS phrase specifies optional parameters for the function.

Parameters

delimiter
VARCHAR

Single delimiter character.

Default value: |

header_names
VARCHAR

[Optional] Specifies header names for columns.

Default value: ucoln

Where n is the column offset number, starting with 0 for the first column. The function uses default values if you do not specify values for the header_names parameter.

trim
BOOLEAN

[Optional] Trims white space from header names and field values.

Default value: true

treat_empty_val_as_null
BOOLEAN

[Optional] Specifies that empty fields become NULLs, rather than empty strings ('').

Default value: true

Examples

These examples use a short set of delimited data:   

Name|CITY|New city|State|zip
Tom|BOSTON|boston|MA|01
Eric|Burlington|BURLINGTON|MA|02
Jamie|cambridge|CAMBRIDGE|MA|08

To begin, save this data as delim.dat.

  1. Create a flex table, dflex
  2. => CREATE flex table dflex();
    CREATE TABLE
  3. Use COPY to load the delim.dat file. Use the flex tables fdelimitedparser with the header='false' option: 
  4. => COPY dflex from '/home/release/kmm/flextables/delim.dat' parser fdelimitedparser(header='false');
     Rows Loaded
    -------------
    	     4
    (1 row)
    
  5. Create a columnar table, dtab, with an identity id column, a delim column, and a column to hold a VMap, named vmap
  6. => CREATE table dtab (id IDENTITY(1,1), delim varchar(128), vmap long varbinary(512));
    CREATE TABLE
    
  7. Use COPY to load the delim.dat file into the dtab table. For the mapdelimitedextractor function, add a header row with USING PARAMETERS header_names= option to specify the header row for the sample data, along with delimiter '!' :
  8. => COPY dtab(delim, vmap as mapdelimitedextractor(delim 
       USING PARAMETERS header_names='Name|CITY|New City|State|Zip')) FROM '/home/dbadmin/data/delim.dat' DELIMITER '!';
     Rows Loaded
    -------------
               4
    (1 row)
    
  9. Use maptostring for the flex table dflex to view the __raw__ column contents. Notice the default header names in use (ucol0ucol4), since you specified header='false' when you loaded the flex table: 
  10. => SELECT maptostring(__raw__) from dflex limit 10;
                                  maptostring                                   
    -------------------------------------------------------------------------------------
     {
       "ucol0" : "Jamie",
       "ucol1" : "cambridge",
       "ucol2" : "CAMBRIDGE",
       "ucol3" : "MA",
       "ucol4" : "08"
    }
    
     {
       "ucol0" : "Name",
       "ucol1" : "CITY",
       "ucol2" : "New city",
       "ucol3" : "State",
       "ucol4" : "zip"
    }
    
     {
       "ucol0" : "Tom",
       "ucol1" : "BOSTON",
       "ucol2" : "boston",
       "ucol3" : "MA",
       "ucol4" : "01"
    }
    
     {
       "ucol0" : "Eric",
       "ucol1" : "Burlington",
       "ucol2" : "BURLINGTON",
       "ucol3" : "MA",
       "ucol4" : "02"
    }
    
    (4 rows)
    
  11. Use maptostring again, this time with the dtab table's vmap column. Compare the results of this output to those for the flex table. Note that maptostring returns the header_name parameter values you specified when you loaded the data: 
  12. => SELECT maptostring(vmap) from dtab;
                                                          maptostring
    ------------------------------------------------------------------------------------------------------------------------
     {
       "CITY" : "CITY",
       "Name" : "Name",
       "New City" : "New city",
       "State" : "State",
       "Zip" : "zip"
    }
    
     {
       "CITY" : "BOSTON",
       "Name" : "Tom",
       "New City" : "boston",
       "State" : "MA",
       "Zip" : "02121"
    }
    
     {
       "CITY" : "Burlington",
       "Name" : "Eric",
       "New City" : "BURLINGTON",
       "State" : "MA",
       "Zip" : "02482"
    }
    
     {
       "CITY" : "cambridge",
       "Name" : "Jamie",
       "New City" : "CAMBRIDGE",
       "State" : "MA",
       "Zip" : "02811"
    }
    
    (4 rows)
  13. Query the delim column to view the contents differently: 
  14. => SELECT delim from dtab;
                    delim
    -------------------------------------
     Name|CITY|New city|State|zip
     Tom|BOSTON|boston|MA|02121
     Eric|Burlington|BURLINGTON|MA|02482
     Jamie|cambridge|CAMBRIDGE|MA|02811
    (4 rows)
    

See Also