Hi,
As per your DB design.... Its difficult to do like this operation. If possible change the single column to 3 columns like Qty1,Qty2 and Rate.
Try this below code....
DECLARE @Test AS TABLE (Data VARCHAR(200))
DECLARE @Qty1 INT , @Qty2 INT
SELECT @Qty1=6,@Qty2=10
INSERT INTO @Test (Data)
SELECT '1 | 5 | 900'
UNION ALL SELECT '6 | 10 | 800'
UNION ALL SELECT '11 | 19 | 600'
SELECT Data FROM @Test
SELECT LTRIM(RTRIM(M.Qty1)) 'Qty1',
LTRIM(RTRIM(M.Qty2)) 'Qty2',
SUBSTRING(M.Rate,CHARINDEX('|',M.Rate,1)+1,LEN(M.Rate)) 'Rate'
FROM (SELECT SUBSTRING(Data,1,CHARINDEX('|',Data,1)-1) 'Qty1',
SUBSTRING(Data,CHARINDEX('|',Data,1)+1,CHARINDEX('|',Data,CHARINDEX('|',Data,1)+1)-CHARINDEX('|',Data,1)-1) 'Qty2',
SUBSTRING(Data,CHARINDEX('|',Data,1)+1,LEN(Data)) 'Rate'
FROM @Test) M
WHERE M.Qty1 = @Qty1 AND M.Qty2 =@Qty2
Note : same this we can use UDF for split process and do like this logic.
DECLARE @Input VARCHAR(50)
SELECT @Input =CAST(@Qty1 AS VARCHAR(10))+'|'+CAST(@Qty2 AS VARCHAR(10))+'|'
SELECT REPLACE(Data,' ', '') 'Data' ,
LTRIM(RTRIM(REPLACE(REPLACE(Data,' ', ''),@Input,''))) 'Rate'
FROM @Test
WHERE REPLACE(Data,' ', '') LIKE @Input+'%'
Regards,
GVPrabu