lol, sorry, blame that on user (me) error 😉. Thanks again

Thank you @Jeffrey Drumm that worked! 

Nevermind my question... just realized there was something else wrong with my class. Thanks

@Chris Stewart 
THANK YOU. This worked!

@Chris Stewart 
I have updated my post and hopefully this gives some more clarity. 

I did try your suggestion with a few minor adjustments to the ones in bold but it did not pull anything unfortunately. 

SELECT *                      
FROM PhysTable                        
WHERE ProviderName = 'DOE, JOE' AND Type = 'NPI' 
AND ProvId =(
             Select ProvId 
             from PhysTable 
             where ProviderName = 'DOE,JOE' 
              AND Type='NPI' 
              and IdentityId = '345678'
            )

@David Hockenbroch you are correct. Its quite frustrating as I want that second row. 
 

@David Hockenbroch 
I realize why I confused you, sorry. So YES we do get the IdentityId of "345678" of Type NUM but we do not get the IdentityId of "12345" or "345678" of Type NPI. 

@David Hockenbroch 
When I run 

SELECT IdentityId                       
FROM PhysTable                        
WHERE ProviderName = 'DOE, JOE' AND Type = 'NPI'    

I get two rows

@David Hockenbroch Everything else correct but one correction - what I want is the IdentityId

@David Hockenbroch I tried this before and it doesn't pull anything unfortunately 

@Chris Stewart I initially marked this as the correct answer but I just realized that was incorrect. We do not get the ProvId "8252" in the message, we do get the IdentityId "345678"

Thank you Julius

Thanks Chris 

Thank you @Robert Cemper 

@Herman Slagman this worked. However, when I had more than one repeating values e.g. 

Set x="Purple,Black,Yellow,Pink,Orange,White,a,b,c,d,e,f"
Set y="Red,Green,Orange,Yellow,z,y,x,w,v"

It only returned that Yellow was found in both lists and ignored the Orange. I probably just need to edit the code that you shared. Thank you!

Thank you @David Hockenbroch ,  I will try this out 

It does, thank you @Roger Merchberger 

Thank you @Prasanth.Annamreddy. That worked! 

Thank you @Enrico Parisi @Kurro Lopez @Ambrogio De Lorenzo 
 

Will do, thanks! 

Thanks @Luis Angel Pérez Ramos 
I tried your suggestion and unfortunately it didn't work. For context (no pun intended), both contexts are set as below:

@Enrico Parisi inserting the break was sufficient on top of your previous response and that of @Mary George . Thank you! 

@Enrico Parisi Thank you, sample message below. I would like that if PMP found, send only the first segment containing PMP and ignore the rest. 

MSH|^~\&|MESA_OP|XYZ_HOSPITAL|iFW|ABC_HOSPITAL|040112043835||BAR^P05|0000000001|T|2.3|
EVN||20200420134725||
PID|||3000222452||DOE^JOHN^E||19931114|M||||||||||1546740|666381774|
PV1||I|BRACKENRIDGE|||||023434|||||||||023434|||||||||||||||||||||||||||20031121||
PV2||||||||||||||||||||||||||||||||||||||
OBX|||||4100|
DG1|1||784.3^APHASIA^I9C||200750816|A
DG1|2||784.0^HEADACHE^I9C||200750816|A
DRG|640^^APP-DRG S43 (2024)^^^^I10||015|.0944|1.8|1.7|^^0|^^|0|1|1|Completed|20231204085141|PMP, CODER|CODER||N
ABS|||1||||20231204085141
DRG|640^^APP-DRG S43 (2024)^^^^I10|Admission DRG|015|.0944|1.8|1.7|^^0|^^|0|1|1|Completed|20231204085141|PMP, CODER|CODER||N
ABS|||1||||20231204085141
DRG|640^^APP-DRG S43 (2024)^^^^I10|PPC DRG|015|.0944|1.8|1.7|^^0|^^|0|1|1|Completed|20231204085141|PMP, CODER|CODER||N
ABS|||1||||20231204085141
DRG|640^^APP-DRG S43 (2024)^^^^I10|PPC Admit DRG|015|.2017|||^^0|^^|0|||Completed|20231204085141|PMP, CODER|CODER||N
ABS|||||||20231204085141
DRG|555^^PMP S43 (2024)^^^^I10||015|.2017|3.1|3.1|^^97585.77|^^|1358.36|||Completed|20231204085141|PMP, CODER|CODER||Y
ABS|||||||20231204085141
DRG|555^^PMP S43 (2024)^^^^I10|HAC Dx Included|015|.2017|3.1|3.1|^^97585.77|^^|1358.36|||Completed|20231204085141|PMP, CODER|CODER||N
ABS|||||||20231204085141
DRG|640^^APP-DRG S43 (2024)^^^^I10||015|.0944|1.8|1.7|^^0|^^|0|1|1|Completed|20231204085141|PMP, CODER|CODER||N
ABS|||1||||20231204085141
DRG|640^^APP-DRG S43 (2024)^^^^I10|Admission DRG|015|.0944|1.8|1.7|^^0|^^|0|1|1|Completed|20231204085141|PMP, CODER|CODER||N
ABS|||1||||20231204085141
DRG|640^^APP-DRG S43 (2024)^^^^I10|PPC DRG|015|.0944|1.8|1.7|^^0|^^|0|1|1|Completed|20231204085141|PMP, CODER|CODER||N
ABS|||1||||20231204085141
DRG|640^^APP-DRG S43 (2024)^^^^I10|PPC Admit DRG|015|.2017|||^^0|^^|0|||Completed|20231204085141|PMP, CODER|CODER||N
ABS|||||||20231204085141

Thank you @Enrico.Parisi. This also worked. I followed your instructions to type in the name of the router since it wasn't appearing in the drop-down menu. 

@Enrico Parisi thank you for your response. However, I think it only works if the trace had been set up on the component and not in the BPL. I tried out your suggestion and it didn't work unfortunately as I could not select the BPL in the Source Config Item drop down menu. 

This worked! Thank you @Oliver Wilms