SQL SELECT field contains(words) does not work

Question

I have this query:

SELECT *
FROM
  (SELECT ' ' + REPLACE(Title,' ','  ') + ' ' AS Title
   FROM MyTable) t
WHERE Title LIKE '% Samsung %'
  AND Title LIKE '% Galaxy %'
  AND Title LIKE '% Axiom %'
  AND REPLACE(REPLACE(REPLACE(Title,' Samsung ',''),' Galaxy ',''), ' Axiom ','') = ''

This query should search in MyTable field Title and dispay all rows which contain the words specified in LIKE.

I don't get any error, but the Field Title contains a row with the following string 'Samsung Galaxy Axiom R830' and my query does not return it (and it should).

This was my original question, it worked for some records, but not for all SQL SELECT LIKE containing only specific words

Doesn't your final condition effectively say that the string must ONLY contain Samsung, Galaxy, and Axiom? the string you give would have 'R830' left over. — Hellion, Jan 17 '13 at 15:22
@Art - That's not the actual problem here. If he wants to ensure a given column in a row has _all_ of those words, he'll need the `AND`. His real problem is that some of the comparison strings were miffed. — Clockwork-Muse, Jan 17 '13 at 16:45

score 3 · Answer 1 · answered Jan 17 '13 at 15:20

3

Could it be because you are looking for "Samsung" with a space before & after and the string does not have a space before "Samsung" ?

answered Jan 17 '13 at 15:20

PaulStock

11,053
9
49
52

No, it's not that. I have modified the query to look like this and it still does not work. SELECT * FROM (select ' ' + REPLACE(Title,' ',' ') + ' ' as Title from MyTable) t WHERE Title LIKE '%Samsung %' AND Title LIKE '% Galaxy %' AND Title LIKE '% Axiom %' AND REPLACE(REPLACE(REPLACE(Title,'Samsung ',''),' Galaxy ',''),' Axiom ','') = '' – Mario Jan 17 '13 at 15:22
Well, *it is that*. You're using `AND`; how can you expect to find `Samsung` when you're looking for `_Samsung_`? – Kermit Jan 17 '13 at 15:23
please see this question, I got my query from here http://stackoverflow.com/questions/14290857/sql-select-where-field-contains-words – Mario Jan 17 '13 at 15:24

James Osborn · Answer 2 · 2013-01-17T15:35:14.067

2

If the title is "Samsung Galaxy Axiom R830", then the following condition will not be true.

REPLACE(REPLACE(REPLACE(Title,' Samsung ',''),' Galaxy ',''),' Axiom ','') = ''

The replaces as written will output

SamsungAxiom R830

This will not match a blank string.

If you removed the spaces from your replaces, you'll be left with R830 (and possibly some whitespace). As Hellion says in his comment, this is a query that requires the words 'Samsung', 'Galaxy' and 'Axiom' to be the only words in your title.

edited Jan 17 '13 at 15:35

answered Jan 17 '13 at 15:21

James Osborn

1,275
7
12

but it worked for some records, I got this query here in another stackoverflow question – Mario Jan 17 '13 at 15:23

score 2 · Accepted Answer · answered Jan 17 '13 at 15:23

You have spaces in the like. Perhaps you want something like:

WHERE (Title LIKE '% Samsung %' or title like '%Samsung' or title like 'Samsung%')
  AND (Title LIKE '% Galaxy %' or title like '%Galazy' or title like 'Galaxy%')
  AND (Title LIKE '% Axiom %' or title like '%Axiom' or title like 'Axiom%')
  AND replace(REPLACE(REPLACE(REPLACE(Title,'Samsung',''),'Galaxy',''), 'Axiom',''), ' ') = ''

Actually, as I think about it, I think the final replace is sufficient:

where replace(REPLACE(REPLACE(REPLACE(Title,'Samsung',''),'Galaxy',''), 'Axiom',''), ' ') = ''

SQL SELECT field contains(words) does not work

3 Answers3