3.61 mtonull - Substitute Value for NULL

Specify the target field parameter at f= , substitute the value at the v= parameter with NULL in the data. Select the methods for finding patterns using exact string matching (the default) or substring matching (-sub option).

Format

mtonull f= v= [-sub] [-W] [i=] [o=] [-nfn] [-nfno] [-x] [--help] [--version]

Parameters

`f=`	specify list of field name(s) (multiple fields can be specified) where the values are replaced .
`v=`	Specify the character string value (multiple items can be specified) for matching the field defined at `f=` parameter.
	Replace the matched values with NULL.

Options

`-sub`	Rather than matching the exact string,
	compare character substring with the values in the field specified in the `f=` parameter
	and replace the value defined in the `v=` parameter containing the substring with NULL value.
`-W`	Substring match for wide character strings when the `-sub` option is specified.

Examples

Example 1: Basic Example

Replace 0 with NULL value in columns quantity and price.

$ more dat1.csv
item,quantity,price
A,0,1
B,1,0
C,2,200
D,3,0
E,0,298
$ mtonull f=quantity,price v=0 i=dat1.csv o=rsl1.csv
#END# kgtonull f=quantity,price i=dat1.csv o=rsl1.csv v=0
$ more rsl1.csv
item,quantity,price
A,,1
B,1,
C,2,200
D,3,
E,,298

Example 2: Replace a specified number with NULL value

Replace 0 or 1 with NULL value in columns quantity and price.

$ mtonull f=quantity,price v=0,1 i=dat1.csv o=rsl2.csv
#END# kgtonull f=quantity,price i=dat1.csv o=rsl2.csv v=0,1
$ more rsl2.csv
item,quantity,price
A,,
B,,
C,2,200
D,3,
E,,298

Example 3: Substitute substring match

Replace with a NULL value where quantity and price columns contain 0.

$ mtonull -sub f=quantity,price v=0 i=dat1.csv o=rsl3.csv
#END# kgtonull -sub f=quantity,price i=dat1.csv o=rsl3.csv v=0
$ more rsl3.csv
item,quantity,price
A,,1
B,1,
C,2,
D,3,
E,,298

Example 4: Substitute character string

Replace the string in the item field that matches character string apple, orange, pineapple with NULL value.

$ more dat2.csv
item,price
fruit:apple,100
fruit:peach,250
fruit:grape,300
fruit:pineapple,450
fruit:orange,500
$ mtonull f=item v=apple,orange,pineapple -sub i=dat2.csv o=rsl4.csv
#END# kgtonull -sub f=item i=dat2.csv o=rsl4.csv v=apple,orange,pineapple
$ more rsl4.csv
item,price
,100
fruit:peach,250
fruit:grape,300
,450
,500

Related Command

mnullto : Reversely, replace NULL value with a character string.