How to join two CSV files in Powershell with SQL LIKE syntax-CodePudding

I have two CSV files:

CSV_1:

Name,Age,
John,20
Amy,25
Joe,30

CSV_2:

Name,Address
JohnDoe,123 street
AmyDoe,456 street
JoeSmith,789 drive

Is there a way to join them together in Powershell using SQL LIKE syntax?

For example:

select *, csv2.address from csv1
left join csv2 on csv2.Name LIKE (csv1.Name   '%%')

I tried to use the JOIN-OBJECT module but it doesn't look like there is way to use this kind of filtering. Or maybe I cannot find an example to do it. It has to be done in Powershell due to some restrictions. Any help on this is appreciated.

Thanks

CodePudding user response：

$a = ConvertFrom-Csv @'
Name,Age,
John,20
Amy,25
Joe,30
'@

$b = ConvertFrom-Csv @'
Name,Address
JohnDoe,123 street
AmyDoe,456 street
JoeSmith,789 drive
'@

Using this Join-Object script/Join-Object Module (see also: In Powershell, what's the best way to join two tables into one?), you might do something like this:

$a |Join $b -Using { $Right.Name -Like "$($Left.Name)*" }

Name            Age Address
----            --- -------
{John, JohnDoe} 20  123 street
{Amy, AmyDoe}   25  456 street
{Joe, JoeSmith} 30  789 drive

To separate the names:

$a |Join $b -Using { $Right.Name -Like "$($Left.Name)*" } -Name A,B

AName BName    Age Address
----- -----    --- -------
John  JohnDoe  20  123 street
Amy   AmyDoe   25  456 street
Joe   JoeSmith 30  789 drive

Note that besides the fact that there is a risk that e.g. a Jon will be matched with a JonathanDoe, the -Using parameter is rather slow.
If the full names are actually in camelCase or PascalCase, it will be faster to strip off the surname first (which might be done for both sides):

'JohnDoe' -CReplace '(?<=. )[A-Z] .*'
John

$a |Join $b -On { $_.Name -CReplace '(?<=. )[A-Z] .*' } -Name A,B

AName BName    Age Address
----- -----    --- -------
John  JohnDoe  20  123 street
Amy   AmyDoe   25  456 street
Joe   JoeSmith 30  789 drive