Loading

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

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

ahmed elbarbary

6y766

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 ?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Junaid Musla6y ago
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 elbarbary6y ago
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 Musla6y ago
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.