I try to do with my table tbl_features
DECLARE @SearchName VARCHAR(200) = 'gp 2 tablets', @i INT = 1
CREATE TABLE #temp1([img]nvarchar(max),[pname] VARCHAR(200),[qty] nVARCHAR(max),[id] nVARCHAR(400))
WHILE @i <= (SELECT COUNT(Item) FROM dbo.SplitString(@SearchName, ' '))
BEGIN
DECLARE @Item VARCHAR(100)
SELECT @Item = t.Item
FROM (SELECT Item,ROW_NUMBER() OVER(ORDER BY (SELECT 0)) as R_Num
FROM dbo.SplitString(@SearchName, ' ')) t
WHERE t.R_Num = @i
IF @Item <> ''
BEGIN
INSERT INTO #temp1
SELECT imgpath,pname,fid,pid FROM tbl_features as f
WHERE pname LIKE '%' + @Item + '%' and f.status=1 and f.gid=0 and f.fid=0 and f.pfid=0 and f.subcatid=0 and f.gid=0 and f.saltid=0 order by len(f.pname)
END
SET @i = @i + 1
END
SELECT DISTINCT * FROM #temp1
DROP TABLE #temp1
DROP TABLE #temp
but result come
UploadingImg/noimage.png Abmont 3D 10mg/120mg/200mg Tablet-10 tablets in 1 strip 0 72
UploadingImg/noimage.png Abmont LC 5mg/10mg Tablet-10 tablets in 1 strip 0 62
UploadingImg/noimage.png Alday 10mg Tablet-10 tablets in 1 strip 0 114
UploadingImg/noimage.png Allermax 5 mg/10 mg Tablet-10 tablets in 1 strip 0 562
UploadingImg/noimage.png Amaryl M 1mg Tablet PR-20 Tablet per in 1 strip 0 80
UploadingImg/noimage.png Amcard 5 Tablet-7 tablets in 1 strip 0 186
UploadingImg/noimage.png Amitone 25mg Tablet-10 tablets in 1 strip 0 106
UploadingImg/noimage.png Anxipose 2mg Tablet 0 122
UploadingImg/noimage.png Aquiti 25mg Tablet-10 tablets in 1 strip 0 117
UploadingImg/noimage.png Argihope Tablet-of10 tablets 0 73
UploadingImg/noimage.png Azikem 200mg/5ml Suspension-15 ml in 1 bottle 0 152
UploadingImg/noimage.png Azikik 200mg Syrup-15 ml in 1 bottle 0 149
and when i search gp 2 tablet it show error
Msg 8152, Level 16, State 30, Line 200 String or binary data would be truncated.
