Full text search with concatenating tables in query

Question

I'm just starting out with full text searching and I've always used wildcards in the past. Here's a query I have:

SELECT rID, claimID+counter FROM claims WHERE claimID+counter LIKE '%ba%' AND (status='submitted' OR status='closed')

I'm trying to convert this to contains but I get an error saying that the column doesn't exist.

SELECT rID, claimID+counter FROM claims WHERE contains(claimID+counter,'ba') AND (status='submitted' OR status='closed')

I tried the above and no go. How do i fix this?

there is not. There is a claimID and counter column. When i do a wildcard search of claimID+counter I don't have any issues. Only happens when I use contains. — Damien, Feb 04 '15 at 14:47

score 0 · Answer 1 · edited May 23 '17 at 12:06

0

Try this

SELECT rID, claimID+counter FROM claims WHERE contains((claimID,counter),'ba') AND (status='submitted' OR status='closed')

If you get error running above script like

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'claims' because it is not full-text indexed.

Then make sure you have full-text search feature installed. Follow this Link for more detail

edited May 23 '17 at 12:06

Community

answered Feb 04 '15 at 15:02

did you try your statement yourself? – Giorgi Nakeuri Feb 04 '15 at 15:07
@GiorgiNakeuri yes i got error and hence i posted the required solution . I always post working solutions – DevelopmentIsMyPassion Feb 04 '15 at 15:08
I think author wants to combine those columns and search in combined result. claimID+counter LIKE '%ba%'. so claim may be b, counter may be a. and claimID+counter = 'ba'. contains((claimID,counter),'ba') is different.. – Giorgi Nakeuri Feb 04 '15 at 15:15
Hey Giorgi... you are exactly right. How do i pull this off? – Damien Feb 04 '15 at 15:16
@Damien, I am not sure if SQL Server supports such functionality – Giorgi Nakeuri Feb 04 '15 at 15:24

1 Answers1