Select record that start and end with specific string in SQL Server

Hi ahmedsa,

Refer below query.

SQL

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')

DECLARE @Counter INT, @TotalCount INT, @AffectedProduct VARCHAR(100)
SET @Counter = 1

DECLARE @DynamicQuery AS NVARCHAR(MAX)
SET @DynamicQuery = 'SELECT * FROM #PartsData WHERE '
 
SET @TotalCount = (SELECT COUNT(*) FROM #searchdata) 
WHILE (@Counter <= @TotalCount)
BEGIN
    SET @AffectedProduct = (SELECT AffectedProduct FROM #searchdata WHERE Id = @Counter)
 
    SET @DynamicQuery += 'PartNumber LIKE ''' + 
						SUBSTRING(@AffectedProduct,0,CHARINDEX('*',@AffectedProduct,0)) + 
						'%[a-z0-9]%' + 
						SUBSTRING(@AffectedProduct,CHARINDEX('*',@AffectedProduct,0)+3,LEN(@AffectedProduct)) + ''' OR '
    SET @Counter = @Counter + 1
    CONTINUE;
END

SET @DynamicQuery = LEFT(@DynamicQuery, LEN(@DynamicQuery)-2)
EXEC(@DynamicQuery)

DROP TABLE #searchdata
DROP TABLE #PartsData

Output