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

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  October 6, 2020 at 12:46 am

  #3793062

  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 ?

• Jeff Moden

  SSC Guru

  Points: 1004324

  More actions

  October 6, 2020 at 1:16 am

  #3793068

  I'll give you the same answer that I posted to you over on SQLTeams about a half hour ago...

  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.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)