×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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 .

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 ?

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

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

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"

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close