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

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

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

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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Peter Nordberg

ASKER

Vitor,

Yes that's right.

SOLUTION

Naitik Gamit

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

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