Avatar of Peter Nordberg
Peter Nordberg
Flag for Sweden 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å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%'

Open in new window


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

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
Vitor Montalvão

For exact match use the '=' sign:
SELECT * FROM customer WHERE index = 'trädgård'

Open in new window

Peter Nordberg

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
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'

Open in new window

Regards
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Naitik Gamit

You can use CHARINDEX as below :
SELECT * FROM customer WHERE CHARINDEX('trädgård',index) > 0 

Open in new window

Vitor Montalvão

Is the ';' separating the words? If so you can use LIKE as follow:
SELECT * FROM customer WHERE index LIKE '%trädgård;%blommor;'

Open in new window

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

Peter Nordberg

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Naitik Gamit

Hi Peter

Try As :

SELECT * FROM customer WHERE CHARINDEX(',trädgård,',',' + index + ',') > 0

Open in new window

This will work as you need..........................!!
Peter Nordberg

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
Naitik Gamit

Both of words 'trädgård' and 'blommor' in one column or may be in defferent ?

For example
'trädgård,blombukett,blommor' OR in different as
'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

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Peter Nordberg

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
Vitor Montalvão

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
Rgonzo1971

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Peter Nordberg

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Vitor Montalvão

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Peter Nordberg

ASKER
Vitor,

Yes that's right.
SOLUTION
Naitik Gamit

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

Yes that's right.
So did you try my last suggestion with the word delimiter character included?
Peter Nordberg

ASKER
Yes, works better.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Vitor Montalvão

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?
Vitor Montalvão

peternordberg, is this issue solved?
If so, please close this question by accepting the comment or comments that helped you out.
Cheers