SQL SELECT field contains(words) does not work

0
votes

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

3
sqlselectsql-like
This won't match because you're asking for ` Samsung`. – Kermit
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
Must use OR instead of AND in your WHERE - SQL basics. – Art
@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

3 Answers

2
votes

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',''), ' ') = ''
3
votes

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

2
votes

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.