How to select data based dynamically based on start and end of AffectedProduct ?

Question

I work on SQL server 2012 I need to get data from #partsdata table where part number matches
Affected Product both start and End

as Example where I have Affected Product as

APAMS-***G

then I will get Parts That have start APAMS- and End by G

start must be end with dash (-)

this roles applied to another rows on search data table .

I get part number that have matched with start f affected product and end with same charachter of affectedproduct

create table #searchdata
(
Id int,
AffectedProduct nvarchar(50)
)
insert into #searchdata(Id,AffectedProduct)
values
(1,'APAMS-***G'),
(2,'APg-***F'),
(3,'Dom-***D')


create table #PartsData
(
PartId int,
PartNumber nvarchar(50)
)
insert into #PartsData(PartId,PartNumber)
values 
(233,'APAMS-234G'),
(501,'APAMS-901G'),
(909,'APAMS-901G'),
(700,'APg-670F'),
(550,'APg-G3DF'),
(940,'APg-321F'),
(702,'Dom-670D'),
(710,'Dom-G3DD'),
(770,'APg-321L'),
(915,'APAMS-901M'),
(922,'APg-325N')

Expected Result

PartId  PartNumber
233 APAMS-234G
501 APAMS-901G
909 APAMS-901G
700 APg-670F
550 APg-G3DF
940 APg-321F
702 Dom-670D
710 Dom-G3DD

so How to do that please ?

Answer 1

Hi @ahmed salah ,

Please refer the updated query from below and check whether it is working.

If not, please provide more sample data with different conditions and expected output.

select a.PartId,a.PartNumber from #PartsData a  
       inner join #searchdata b   
       on a.PartNumber like   
	   ---first part like APAMS- or APg- or Dom-  
	   left(AffectedProduct,Charindex('-',AffectedProduct))+  
	   ---remaining part like ***G or ***F or ***D  
        replace(right(AffectedProduct,len(AffectedProduct)-(Charindex('-',AffectedProduct))),'*','_')  

Or

  select a.PartId,a.PartNumber from #PartsData a  
    inner join #searchdata b   
    on a.PartNumber like   
	---first part like APAMS- or APg- or Dom-  
	left(AffectedProduct,Charindex('-',AffectedProduct))  
	--second part after '-'  like ***  
     +replace(substring(AffectedProduct,Charindex('-',AffectedProduct)+1,len(AffectedProduct)-(Charindex('-',AffectedProduct))-1),'*','_')  
	---last part like  G or F or D  
	 +RIGHT(AffectedProduct,1)  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @ahmed salah ,

Please refer below:

  select a.PartId,a.PartNumber from #PartsData a  
  inner join #searchdata b   
  on a.PartNumber like replace(b.AffectedProduct,'*','_')  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

if I need first part only as
APAMS- ,APg-,Dom-
meaning i need to filter by first part of word until dash
APAMS-234G i need to search by APAMS-
so how to do that by dynamic way

Answer 4

Just to be clear, see the documentation for "LIKE":

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver15

Replacing '*' with '_' and using LIKE will give you the results you want.

Answer 5

how to get APAMS- from APAMS-234G
really i need to search by APAMS- to get

PartId    PartNumber
 233    APAMS-234G
 501    APAMS-901G
 909    APAMS-901G

and to search by APg- to get

700    APg-670F
 550    APg-G3DF
 940    APg-321F

and search by Dom-
702 Dom-670D
710 Dom-G3DD
to summarize search by start until end with dash

by dynamic way