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: Where n is the column offset number, starting with |
trim |
BOOLEAN |
[Optional] Trims white space from header names and field values. Default value: |
treat_empty_val_as_null |
BOOLEAN |
[Optional] Specifies that empty fields become Default value: |
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
.
- Create a flex table,
dflex
: - Use COPY to load the
delim.dat
file. Use the flex tablesfdelimitedparser
with theheader='false'
option: - Create a columnar table,
dtab
, with an identityid
column, adelim
column, and a column to hold a VMap, namedvmap
: - Use COPY to load the
delim.dat
file into thedtab
table. For themapdelimitedextractor
function, add a header row withUSING PARAMETERS header_names=
option to specify the header row for the sample data, along withdelimiter '!'
: - Use
maptostring
for the flex tabledflex
to view the__raw__
column contents. Notice the default header names in use (ucol0
–ucol4
), since you specifiedheader='false'
when you loaded the flex table: - Use
maptostring
again, this time with thedtab
table'svmap
column. Compare the results of this output to those for the flex table. Note thatmaptostring
returns theheader_name
parameter values you specified when you loaded the data: - Query the
delim
column to view the contents differently:
=> CREATE flex table dflex(); CREATE TABLE
=> COPY dflex from '/home/release/kmm/flextables/delim.dat' parser fdelimitedparser(header='false'); Rows Loaded ------------- 4 (1 row)
=> CREATE table dtab (id IDENTITY(1,1), delim varchar(128), vmap long varbinary(512)); CREATE TABLE
=> 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)
=> 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)
=> 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)
=> 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)