4.63 match - Search

Format 1: match(search string , $str_1,str_2,\cdots$ )

Format 2: matcha(search string , $str_1,str_2,\cdots$ )

Format 3: matchs(search string , $str_1,str_2,\cdots$ )

Format 4: matchas(search string , $str_1,str_2,\cdots$ )

Search for the search string in $str_1,str_2,\cdots$ , and returns true if there is a match and false otherwise.

OR search and AND search returns partial match and exact match of string. The corresponding functions are shown in Table 4.21.

Table 4.22: Input Data

	OR search	AND search
Exact match	match	matcha
Partial match	matchs	matchas

The match function returns true if there is an exact match of the specified character string with any of the string in $str_1,str_2,\cdots$ . The matcha function returns true if there is an exact match with all strings in $str_1,str_2,\cdots$ . matchs function returns true if there is partial match with any of the string in $str_1,str_2,\cdots$ . matchas function returns true if there is partial match with all strings in $str_1,str_2,\cdots$ . Refer to Table 4.8 on the boolean table of OR/AND logical operation for NULL values.

Examples

Example 1: OR exact match

Returns true if either column f1,f2,f3 contains 1.

$ more dat1.csv
id,f1,f2,f3
1,1,1,1
2,1,0,1
3,,,
4,1,,1
$ mcal c='match("1",$s{f1},$s{f2},$s{f3})' a=rsl i=dat1.csv o=rsl1.csv
#END# kgcal a=rsl c=match("1",$s{f1},$s{f2},$s{f3}) i=dat1.csv o=rsl1.csv
$ more rsl1.csv
id,f1,f2,f3,rsl
1,1,1,1,1
2,1,0,1,1
3,,,,0
4,1,,1,1

Example 2: AND exact match

Returns true if columns f1,f2,f3 contains the character "1".

$ mcal c='matcha("1",$s{f1},$s{f2},$s{f3})' a=rsl i=dat1.csv o=rsl2.csv
#END# kgcal a=rsl c=matcha("1",$s{f1},$s{f2},$s{f3}) i=dat1.csv o=rsl2.csv
$ more rsl2.csv
id,f1,f2,f3,rsl
1,1,1,1,1
2,1,0,1,0
3,,,,0
4,1,,1,0

Example 3: OR partial match

Returns true if the character string ab exists in either column s1,s2,s3.

$ more dat2.csv
id,s1,s2,s3
1,ab,abx,x
2,abc,xaby,xxab
3,,,
4,#ac,x,x
$ mcal c='matchs("ab",$s{s1},$s{s2},$s{s3})' a=rsl i=dat2.csv o=rsl3.csv
#END# kgcal a=rsl c=matchs("ab",$s{s1},$s{s2},$s{s3}) i=dat2.csv o=rsl3.csv
$ more rsl3.csv
id,s1,s2,s3,rsl
1,ab,abx,x,1
2,abc,xaby,xxab,1
3,,,,0
4,#ac,x,x,0

Example 4: AND partial match

Returns true if the character string ab exists in columns s1,s2,s3.

$ mcal c='matchas("ab",$s{s1},$s{s2},$s{s3})' a=rsl i=dat2.csv o=rsl4.csv
#END# kgcal a=rsl c=matchas("ab",$s{s1},$s{s2},$s{s3}) i=dat2.csv o=rsl4.csv
$ more rsl4.csv
id,s1,s2,s3,rsl
1,ab,abx,x,0
2,abc,xaby,xxab,1
3,,,,0
4,#ac,x,x,0

Example 5: Search for NULL value

Return true if str column contains NULL value.

$ mcal c='match(nulls(),$s{s1},$s{s2},$s{s3})' a=rsl i=dat2.csv o=rsl5.csv
#END# kgcal a=rsl c=match(nulls(),$s{s1},$s{s2},$s{s3}) i=dat2.csv o=rsl5.csv
$ more rsl5.csv
id,s1,s2,s3,rsl
1,ab,abx,x,0
2,abc,xaby,xxab,0
3,,,,1
4,#ac,x,x,0