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
Talk To Other Members
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