Find a character in a string using Powershell?

Question

I know I could use Contains to find it but it doesn't work.

Full Story:
I have to get the PartNo, Ver, Rev from SQl db and check if they occur in the first line of the text file. I get the first line of the file and store it in $EiaContent. The PartNo is associated with MAFN as in $partNo=Select PartNo Where MAFN=xxx. Most of the time MAFN returns one PartNo. But in some cases for one MAFN there could be multiple PartNo. So the query returns multiple PartNo(PartNo_1,PartNo_2,PartNo_3,and PartNo_4) but only one of these will be in the text file.

The issue is that each of these PartNo. is treated as a single character in PowerShell. $partNo.Length is 4. Therefore, my check If ($EiaContent.Contains("*$partNo*")) fails and it shouldn't in this case because I can see that one of the PartNo is mentioned in the file. Also, Contains wouldn't work if there was one PartNo. I use like as in If ($EiaContent -like "*$partNo*") to match the PartNo and it worked but it doesn't work when there are multiple PartNo.

Data type of $partNo is string and so is $EiaContent. The data type of PartNo. in SQL is varchar(50) collation is COLLATE SQL_Latin1_General_CP1_CI_AS I am using PowerShell Core 7.2 and SQL 2005

Code:

    $EiaContent = (Get-Content $aidLibPathFolder\$folderName\$fileName  -TotalCount 1)
    Write-host $EiaContent
    #Sql query to get the Part Number 
    $partNoQuery = "SELECT PartNo FROM [NML_Sidney].[dbo].[vMADL_EngParts] Where MAFN = $firstPartTrimmed"
    $partNoSql = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $partNoQuery
    #Eliminate trailing spaces
    $partNo = $partNoSql.PartNo.Trim()
    If ($EiaContent.Contains("*$partNo*")) {
       Write-Host "Part Matches"
    }
    Else {
       #Send an email stating the PartNo discrepancy 
    }

Thank you in advance to those who try to help.

EDIT Screenshot [1]: https://i.sstatic.net/hIqJB.png

A1023 A1023MD C0400 C0400MD is the output of the variable $partNo and O40033( C0400 REV N VER 004, 37 DIA 4.5 BRAKE DRUM OP3 ) is the output of the variable $EiaContent

Answer 1

So the query returns multiple PartNo(PartNo_1,PartNo_2,PartNo_3,and PartNo_4) but only one of these will be in the text file.

A1023 A1023MD C0400 C0400MD is the output of the variable $partNo and O40033( C0400 REV N VER 004, 37 DIA 4.5 BRAKE DRUM OP3 ) is the output of the variable $EiaContent

So you first have to split $partNo and then for each sub string of $partNo, search for it in $EiaContent:

If ($partNo -split ' ' | Where-Object { $EiaContent.Contains( $_ ) }) {
   Write-Host "Part Matches"
}

This is the generic form that most people are used to. We can simplify the query using the unary form of -split (as we split on the default separator) and use the intrinsic array method .Where() which is faster as it does not involve pipeline overhead.

If ((-split $partNo).Where{ $EiaContent.Contains( $_ ) }) {
   Write-Host "Part Matches"
}

As correctly noted in comments, wildcards are not supported by the .Contains() string method.

Wildcards are supported only by the PowerShell -like operator. The following example is just for educational purposes, I wouldn't use it in your case as .Contains() string method is simpler and faster.

If ((-split $partNo).Where{ $EiaContent -like "*$EiaContent*" }) {
   Write-Host "Part Matches"
}

Note that -contains would not be suitable here. A common misconception is that -contains does a substring search, when the LHS operand is a string. It doesn't! The operator tests whether a collection (such as an array) on the LHS contains the value given on the RHS.