asked on Search query matching words
Hi,
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årdsr
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:
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.
Thanks for help!
Peter
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årdsr
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.
Thanks for help!
Peter
Microsoft SQL ServerSQL
Last Comment
Vitor Montalvão
ASKER
Hi,
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
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
Hi,
pls try
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'
You can use CHARINDEX as below :
SELECT * FROM customer WHERE CHARINDEX('trädgård',index) > 0
Is the ';' separating the words? If so you can use LIKE as follow:
SELECT * FROM customer WHERE index LIKE '%trädgård;%blommor;'
SELECT *
FROM customer
WHERE index LIKE '%trädgård%' AND index LIKE '%blommor%'
ASKER
Hi Natik Gamit,
The idea of Charindex is tempting but for what I see it also takes the results of 'trädgårdsg.' and the like.
Peter
The idea of Charindex is tempting but for what I see it also takes the results of 'trädgårdsg.' and the like.
Peter
Hi Peter
Try As :
Try As :
SELECT * FROM customer WHERE CHARINDEX(',trädgård,',',' + index + ',') > 0
ASKER
Natik Gamik,
Worked better but how do I do it if I have more than one search word? For example 'trädgård' and 'blommor'
Peter
Worked better but how do I do it if I have more than one search word? For example 'trädgård' and 'blommor'
Peter
Both of words 'trädgård' and 'blommor' in one column or may be in defferent ?
For example
'trädgård,blombukett,blomm
'trädgård,blombukett'
'trädgårdsresa,blommor'
If both in same column then you can use comma seprated value in above query as
For example
'trädgård,blombukett,blomm
'trädgård,blombukett'
'trädgårdsresa,blommor'
If both in same column then you can use comma seprated value in above query as
SELECT * FROM customer WHERE CHARINDEX(',trädgård,blommor,',',' + index + ',') > 0
ASKER
Hi, this seems to work if the words are in that order but if not you reverse it. Is there a fix for that?
Peter
Peter
Hi, this seems to work if the words are in that order but if not you reverse it. Is there a fix for that?Did you try my last suggestion?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hi Vitor,
I tried it but then I get every version of 'trädgård'. 'tärdgårsmaskine', 'trädgårdsodlingar' etc. I don't want that.
Peter
I tried it but then I get every version of 'trädgård'. 'tärdgårsmaskine', 'trädgårdsodlingar' etc. I don't want that.
Peter
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Vitor,
Yes that's right.
Yes that's right.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Yes that's right.So did you try my last suggestion with the word delimiter character included?
ASKER
Yes, works better.
Ok but you said better so I guess isn't producing exactly the expected results. Can you provide examples for the situations that it doesn't work?
peternordberg, is this issue solved?
If so, please close this question by accepting the comment or comments that helped you out.
Cheers
If so, please close this question by accepting the comment or comments that helped you out.
Cheers
Open in new window