How to select data based dynamically based on start and end of Affected Product from Parts Data Table?

Transact-SQL
,
1

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 character of affected product

 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 ?

2

Assuming that each "*" in your search data is supposed to represent a place holder for a single character, the following will work but will also guarantee a table/index scan in the #PartsData table because of the midstring search.

 SELECT pd.*
   FROM #PartsData  pd
   JOIN #searchdata sd ON pd.PartNumber LIKE REPLACE(sd.AffectedProduct,'*','_')
;

A MUCH better way would be to break the part numbers into the 3 parts that they all appear to be made from and a permanent change to how you store data in the table. You could even use persisted computed columns to break each part number into the 3 different parts for much better performance on such ad hoc searches.

1 Like
3

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

4

hello,
something simple like that do the trick :slight_smile:
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')

select * into #result from #PartsData where 1=2

declare curs cursor for select AffectedProduct from #searchdata
declare @searched varchar(30)
declare @cmd varchar(4000)
open curs
fetch next from curs into @searched
while (@@fetch_status = 0)
begin
set @cmd = 'INSERT INTO #result select * from #PartsData where PartNumber like '''+replace(@searched,'*','%')+''''
--print @cmd
exec (@cmd)
fetch next from curs into @searched
end
close curs
deallocate curs

select * from #result

best regards

5

No. Don't do that. You simply don't need a cursor for any of this.

1 Like
6

Like this if only one filter as you've asked for...

 SELECT *
   FROM #PartsData
  WHERE PartNumber LIKE 'APAMS-%'
;

Again, though, it would be much more effective to split the part number column into its 3 component parts.

Shifting gears, please read the following so you know how to use LIKE and its various capabilities.
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver15

1 Like
7

hello,
agree for cursor usage. But this is good for understanding.
Until now, th ejoin went with two table scans.
This is a part of the solution

8

The reason why the join went to two table scans is because ALL of the data in both tables is used and a single scan of each table is the most efficient in such cases. If a cursor is used and there are indexes in place, it will always do index seeks but in a very RBAR fashion and the cost of that will far exceed the two scans.

As for it being good for understanding, I have to agree but only insofar as it being a really good demonstration of why you shouldn't use a cursor, especially for something so simply done in a non-RBAR fashion. The problem I'm having with your post on that is that you didn't say that an someone looking for a solution might actually end up using your cursor for something much larger.

1 Like