Using Like Condition in Join not giving desired output
Try this:
;WITH NewItem AS
(
SELECT
Item
, SearchString = CASE CHARINDEX(' ', Item)
WHEN 0 THEN Item
ELSE LEFT(Item, CHARINDEX(' ', Item)-1)
END
FROM @Item
)
SELECT
Product = P.product
, Date = P.date
, Dose = P.dose
FROM Product P
JOIN NewItem I ON CHARINDEX(SearchString, P.Product) > 0
;