How to display part number based on search string by chemical id ?
How to display part number based on search string by chemical id ?
(OP)
I work on sql server 2019 i face issue i can't get part number that have partstring from table chemical
based on chemicalid
so i get chemicalid from table chemicalParts when part number match part string
star 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 .
expected result will be

what i try is
but this not give me result
so how to solve it please
based on chemicalid
so i get chemicalid from table chemicalParts when part number match part string
star 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 .
CODE --> sql
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
CODE --> sql
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
RE: How to display part number based on search string by chemical id ?
CODE
Well, and then don't look for LIKE '% + StringPart + %', that's wrong in two ways, look for
CODE
Because the StringPart values are already the full LIKE expressions you want to use. Prefixing and Auffixing them with % allows anything that has the searched pattern within, regardless of how it starts and ends. But you want to WA50901 part to start at position 4 and not anywhere. If you want to use % in the LIKE expression, you can simply make it part of the StringPart column value.
Chriss
RE: How to display part number based on search string by chemical id ?
--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row"
RE: How to display part number based on search string by chemical id ?
CODE
select cp.chemicalid,cp.partnumber from #chemicalParts cp inner join #chemical c on c.chemicalid=cp.chemicalid and cp.partnumber like Replace(StringPart, '*', '_')
The underscore character is the single character wilcard, not the ?.
-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom