I have a table looking like this:
customerID int,
Organisation nvarchar
index nvarchar
The data in the index column can look something like this:
trädgård; trädgårdsmaskin;trädgårdsresa,blommor;blombukett;
If a user now searches for 'trädgård' I would like the query only to give the result those result that exactly match that word. That is, I want to be able to parse and compare each word between the ; sign. The same true if the user searches for 'trädgård' and 'blommor' I want it to exaclty match the customer that have those two words in their index column.
If I do something like this it searches the word in the whole string:
SELECT * FROM customer WHERE index LIKE '%trädgård%'
Then I get all instances that contains that word and I don't want it to compare it to each word between the ;. Is that possible and if so how can I do it.
If the index contains all the words I described above it will find nothing. If I use = sign only those where the whole field exaclty match tradgård will show up. I want to find all customers where trädgård is in the field (the field contain that particular word) even if there is also other words in the field.
Peter
Rgonzo1971
Hi,
pls try
SELECT * FROM customer WHERE index = 'trädgård' OR index like 'trädgård;%' OR index like '%;trädgård;%' OR index like '%;trädgård'
But this will returns the rows that has those words in the same order, meaning that if 'blommor' comes first it won't be considered. For that you should use a LIKE per word:
SELECT * FROM customer WHERE index LIKE '%trädgård%' AND index LIKE '%blommor%'
Open in new window