SQL fetch results by concatenating words in column

Question

I have column store_name (varchar). In that column I have entries like prime sport, best buy... with a space. But when user typed concatenated string like primesport without space I need to show result prime sport. how can I achieve this? Please help me

Answer 1

SELECT *
FROM TABLE
WHERE replace(store_name, ' ', '') LIKE '%'+@SEARCH+'%' OR  STORE_NAME LIKE '%'+@SEARCH +'%'