wdudek

My scenario is as follows. I have an application that allows users to search
on multiple columns for one or more values. i.e. account_number, account_name
and order_number can be searched on. Using contains and or containstable, I
have not been able to get this to work as expected. The query seems to be
only returning results for rows that have the value in all of the columns, is
there any way to make it return if any onr of the columns matches? My current
strategy has been to concatenate all the values into one column when the data
is loaded/updated and have the index on this column. While this does work, it
seems like I shouldn't have to do this?

Thanks

Bill

Charles Wang[MSFT]

Hi Bill,
I understand that you would like to return the rows that have the value in
any of the columns.
If I have misunderstood, please let me know.

To narrow down this issue, could you please let us know what your T-SQL
statement was? Did you try OR statement? For example:
SELECT *
FROM tblText
WHERE CONTAINS(account_number, @searchword)
OR CONTAINS(account_name, @searchword)
OR CONTAINS(order_number, @searchword)

Please feel free to let me know if you have any other questions or
concerns. Have a nice day!


Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

ML

Please, post the query. Are all of those columns full-text indexed?


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

wdudek

I was hoping that I wouldn't need to use an OR statement, but I think you
answered my question. Essentially I wanted to use the predicate contains(*,
'*ABC* OR *08117*') where the asterik for the column list would indicate
several columns, some that may have the valeus and some that would not in one
or more records. If an OR statement and multiple contains statements is the
only way to do this, I will continue to concatenate the data into a seperate
field, which is ok as this database is only used for performing searches.

Thanks,

"Charles Wang[MSFT]" wrote:

> Hi Bill,
> I understand that you would like to return the rows that have the value in
> any of the columns.
> If I have misunderstood, please let me know.
>
> To narrow down this issue, could you please let us know what your T-SQL
> statement was? Did you try OR statement? For example:
> SELECT *
> FROM tblText
> WHERE CONTAINS(account_number, @searchword)
> OR CONTAINS(account_name, @searchword)
> OR CONTAINS(order_number, @searchword)
>
> Please feel free to let me know if you have any other questions or
> concerns. Have a nice day!
>
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>

ML

Actually, CONTAINS also accepts a column list.

E.g.:
SELECT *
FROM tblText
WHERE CONTAINS
(
(account_number, account_name, order_number)
,@searchword
)


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

Charles Wang[MSFT]

Yes, this is simpler. First recommend it.

Hi Bill,
Just check with you to see if you need further assistance on this issue.
Please feel free to let us know if you have any questions or concerns. Have
a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support

================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====