CODEWITHSUNDEEP
sqlsql-servert-sql
sanjayan ravi
sanjayan ravi

Reputation: 143

search query that match the parameters exactly

Lets consider below table as an example

Table - subjects 

ID     | subject
------ | ---------------
1      | maths, science
2      | maths, science, english
3      | english, computer

Query 

SELECT * FROM subjects WHERE subject LIKE '%maths%' AND subject LIKE '%science%'

When I run above query the out put would be as follows

ID     | subject
------ | ---------------
1      | maths, science
2      | maths, science, english

My question:

is it possible to modify above query to get the below result that match the search exactly to two parameters, even if the order of the search is changed i.e. 

SELECT * FROM subjects WHERE subject LIKE '%science%' AND subject LIKE '%maths%'

Desired result:

ID     | subject
------ | ---------------
1      | maths, science

Upvotes: 1

Views: 48

Answers (2)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

You can achieve the same result by just using CHARINDEX() as it will check for both existence and you can enforce an order with it.

SELECT * 
FROM subjects 
WHERE CHARINDEX('maths', subject, 0) < CHARINDEX('science', subject, 0) -- enforces order
    AND CHARINDEX('maths', subject, 0) > 0 -- to make sure that your "first word" exists

Upvotes: 1

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

This database design is not good, use association table instead of comma separated list.

You can use LEN to check if there is anything else in the subject except the specified values.

SELECT * FROM subjects WHERE subject LIKE '%science%' AND subject LIKE '%maths%' 
                         AND LEN(subject) <= LEN('maths, science')

Upvotes: 1

Related Questions