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

score 3 · Accepted Answer · answered Nov 13 '13 at 05:52

3

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

answered Nov 13 '13 at 05:52

Amir Keshavarz

3,050
1
19
26

pratim_b · Answer 2 · 2013-11-13T06:04:26.410

1

Have you tried using replace()

You can replace the white space in the query then use like

SELECT * FROM table WHERE replace(store_name, ' ', '') LIKE '%primesport%'

It will work for entries like 'prime soft' querying with 'primesoft'

Or you can use regex.

edited Nov 13 '13 at 06:04

answered Nov 13 '13 at 05:38

pratim_b

1,160
10
29

There is a problem with '. – Amir Keshavarz Nov 13 '13 at 06:00
@AmirrezaKeshavarz will you elaborate? the question asks for only space (" ") – pratim_b Nov 13 '13 at 06:03
LIKE '%primesport%' is true. – Amir Keshavarz Nov 13 '13 at 06:04
After like you should pass string. – Amir Keshavarz Nov 13 '13 at 06:07
thnks fixed the quote – pratim_b Nov 13 '13 at 06:07

score 1 · Answer 3 · answered Nov 13 '13 at 05:51

Well, I don't have much idea, and even I am searching for it. But may be what I know works for you, You can achieve this by performing different type of string operations:

Mike can be Myke or Myce or Mikke or so on.
Cat an be Kat or katt or catt or so on.

For this you should write a function to generate number of possible strings and then form a SQL Query using all these, and query the database.

A similar kind of search in known as Soundex Search from Oracle and Soundex Search from Microsoft. Have a look of it. this may work.

And overall make use of functions like upper and lower.

SQL fetch results by concatenating words in column

3 Answers3