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

[ahmedsa2018]

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 .

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

 

[Chris Miller]

LIKE expressions can contain % for any number of any characters and ? for any, but only a single character. So all you should change is using ? where you used *.

create table #chemical
 (
 chemicalid int,
 StringPart varchar(50)
 )
 insert into #chemical(chemicalid,StringPart)
 values
 (190018,'W3A4?A?????T??'),
 (190300,'VJ0402A?????W1BC'),
 (190700,'???WA50901')

Well, and then don't look for LIKE '% + StringPart + %', that's wrong in two ways, look for

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

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

 

[JeffModen]

Do a search for "t sql like wildcards" (without the quotes) for the documentation and examples of LIKE. It'll be well worth your time.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[gmmastros]

Try this:

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