How to find if a field is contained in another field with Asterix '*'

[ondohotola]

Given input.csv looking like:

a,b
test*and,test*and more
lorem,ipsum
whether,Finding whether a string

mlr --csv put 'if($b=~$a){$test=1}else{$test=0}' input.csv returns

a,b,test
test*and,test*and more,0
lorem,ipsum,0
whether,Finding whether a string,1

which is surprising me.

How can I construct a query to return 1?

[aborruso]

How can I construct a query to return 1?

A way is to escape the *

mlr --csv put 'if($b=~gsub($a,"\*","\*")){$test=1}else{$test=0};$new_a=gsub($a,"\*","\*")' input.csv

+----------+--------------------------+------+-----------+
| a        | b                        | test | new_a     |
+----------+--------------------------+------+-----------+
| test*and | test*and more            | 1    | test\*and |
| lorem    | ipsum                    | 0    | lorem     |
| whether  | Finding whether a string | 1    | whether   |
+----------+--------------------------+------+-----------+

I have added new_a field to show the result of escaping.

[ondohotola]

Thanks, that does the trick nicely

[aborruso]

I have add a great suggestion in stack overflow from Kusalananda: to use ssub function, no regexing, no characters are special:

mlr --csv filter '$b != ssub($b,$a,"")' input.csv

if($b != ssub($b,$a,"")), if after string replace b is not equal to itself, then a is contained in b.

[ondohotola]

Brilliant!

I can't use the filter solution in my particular case as I need concatenate $a . " " . $b . " " . $c with an eventual clean-whitespace.

Unless you can propose something equally elegant, of course :-)-O

[aborruso]

@ondohotola could you share a sample input file and a sample output?

[ondohotola]

Sure, here is a MWE

cat > input.csv <<EOF
Name,Description,Counterparty
ABC*1234 whatever,ABC*1234 whatever something else,NA00FIRN1234567
EOF

mlr -c --from input.csv \
   put 'if ($Name != ssub($Description,$Name,"")) {$NAME = ""}; 
      $DESC = $NAME ." ". $Counterparty ." ". $Description; 
      $desc = $Name ." ". $Counterparty ." ". $Description' \
   then clean-whitespace

which results in the expected

Name,Description,Counterparty,NAME,DESC,desc
ABC*1234 whatever,ABC*1234 whatever something else,NA00FIRN1234567,,NA00FIRN1234567 ABC*1234 whatever something else,ABC*1234 whatever NA00FIRN1234567 ABC*1234 whatever something else

slightly embellished for clarity, I eventually only need $Desc.

[aborruso]

ok @ondohotola I didn't understand you, sorry.

Of course, if you need to create calculated fields, you don't have to use filter, but put.
I think you have solved your problems. Good job

[ondohotola]

No, no, this was brilliant.

Thank you and kusalananda so very much. Not only did this solve my problem, also taught me something :-)-O

I’ll probably even refine it by checking for the $Counterparty as well :-)-O.

This is part of reconciling a bank statement (here obviously bunq) with my accounting software (SQL-Ledger, but via csv2ofx, so it might even be interesting for users of accounting packages that can read OFX).

The original $Description is too short, the concatenated one too long (when duplicated).

[ondohotola]

One can of course also look for "\x2A" if need be :-)-O