How to display part number based on search string by chemical id ?

Question

I work on sql server 2019 i face issue i can't get part number that have partstring from table chemicak
based on chemicalid

so i get chemicalid from table chemicalParts when part number match part string

star or * refere to variable number of charachters meaning * may be 1or 2 or 3 ,etc until 9

or charachter from a to z

it must when get partnumber based on partstring to consider postion of every charachter

so this (190018,'W3A4ZAwgfhlkom') will not display because last charachters after W3A4ZA not have T charachter and must charachters as same position .

create table #chemical  
(  
chemicalid int,  
StringPart varchar(50)  
)  
insert into #chemical(chemicalid,StringPart)  
values  
(190018,'W3A4*A*****T**'),  
(190300,'VJ0402A*****W1BC'),  
(190700,'***WA50901')  
  
  
create table #chemicalParts  
(  
chemicalid int,  
PartNumber varchar(50)  
)  
insert into #chemicalParts(chemicalid,PartNumber)  
values  
(190018,'W3A4yA32198Tgw'),  
(190018,'W3A4kAabczyTdf'),  
(190018,'W3A4ZAwgfhlkom'),  
(190018,'W3A4LA99012Tlm'),  
  
(190300,'VJ0402AkfghyW1BC'),  
(190300,'VJ0402AwydfgW1BC'),  
(190300,'VJ0402Abbt31W1BC'),  
(190300,'VJ0402A59012ftBC'),  
(190300,'VJ0402A304012wBC'),  
  
  
(190700,'123WA50901'),  
(190700,'abcWA50901'),  
(190700,'gfdWA50901'),  
(190700,'klmWA50901'),  
(190700,'90agb50901'),  
(190700,'a317050901')  

expected result will be

what i try is

select cp.chemicalid,cp.partnumber from #chemicalParts cp  
inner join #chemical c on c.chemicalid=cp.chemicalid and cp.partnumber like '% + StringPart + %'  

but this not give me result so how to solve it please

Answer 1

Replace * in the table #chemical with an underscore. This is a wildcard: Any single character

More info:
https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver16

If it is not possible to replace the value of the table, you could also replace it in the query:

 select cp.chemicalid,cp.partnumber from #chemicalParts cp  
 inner join #chemical c on c.chemicalid=cp.chemicalid and cp.partnumber like '%' + REPLACE(StringPart,'*','_') + '%'