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

ahmedsa2018 · Aug 12, 2022

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 .

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

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

Chris Miller · Aug 12, 2022

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 *.

Code:

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

Code:

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 · Dec 18, 2022

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 · Dec 19, 2022

Try this:

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

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

ahmedsa2018

Programmer

Chris Miller

Programmer

JeffModen

Programmer

gmmastros

Programmer

Similar threads

Part and Inventory Search

Sponsor