How to select data based dynamically based on start and end of Affecte

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 ?

Junaid Musla · Answer

so i need to get APAMS-234G when i have APAMS- so how to do that by dynamic way...... To achieve the above stated scenario, You should have a starting characters such as "APAMS-". Using this you can easily fire a query in db, which is almost similar to what i posted earlier: declare @start varchar (20) = 'APAMS-' select * from #PartsData where PartNumber like CONCAT(@start , '%' ) Now, obviously ,if you want to make it dynamic you will have to write the logic as per your requirement. One way would be, getting the perticular @start variable's value from the table #searchdata by the respective object's Id. Once you get this value you could split the value by '-' and get the desired @start value. And fire the above query.

ahmed elbarbary · Answer

thank you for reply if I need first part only as APAMS-, ,APg-,Dom- meaning i need to filter by first part of word until dash so i need to get APAMS-234G when i have APAMS- so how to do that by dynamic way

Junaid Musla · Answer

Hi, As per what i understood from above problem, below is the suggested solution: To fetch for affected product with id 1. declare @start varchar (20) = 'APAMS' ,@ end varchar (20) = 'G' select * from #PartsData where PartNumber like CONCAT(@start , '%' +@ end ) Hope it fullfils you required solution.