Using a function in CONATINS

[RobHudson]

Hi

I have a free text index set up on a column in my table. I need to use a SUBSTRING inside the contains but get the following error:

Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'SUBSTRING'.

This is an exmaple of the clause:

AND NOT CONTAINS([mytable].[mycol], SUBSTRING('abcdefghijk', 2, 3))

If I replace the SUBSTRING with a fixed string ('abcd') it works fine.

Are we not able to use functions inside the CONTAINS statement? Or have I done something wrong?

Cheers
Rob

 

[markros]

Can you use variable in CONTAINS clause ?

 

[hmckillop]

Yes

You have two methods of doing this depending on where your 'abcdefghijk' comes from e.g. if its a passed parameter and only needs changed once then a single substring call into a parameter can do this
e.g.

declare @v_substringPart varchar(20)
.....
AND NOT CONTAINS ([MyTable].[Col], @v_substringPart )

Alternatively if the value is coming from a field in a table you need to include try

SELECT * from MyTable myt
INNER JOIN (select substring(MyCol,2,3) as SubStringField from MyTable) myt2 on myt2.KeyField = myt.keyfield
WHERE
....
AND NOT CONTAINS ([MyTable].[Col], SubStringField )

I am not 100% on the second part as I cant ever remember running CONTAINS using a Column from a table.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[RobHudson]

Thanks hmckillop. It is the 2nd scenario that I need to use where the filter is the column (or SUBSTRING on the column).

Using the INNER JOIN gave a similar error message, I also simplified it by using (and getting the same error):

SELECT *, SUBSTRING([MyCol], 2, 3) AS SubStringField

Incorrect syntax near 'SubStringField'.

It seems that only hardcoded strings or variables can be used...

 

[markros]

Check

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-to-search-for-all-words-inclusive-wi

if it may help for your case.

 

[RobHudson]

Thanks markos
It's quite an interesting way of improving my routine. But I still have the underlying problem of not being able to use SUBSTRING inside a CONTAINS. They are using a LIKE and given the vast amount of data I am dealing with LIKE is extremely slow compared to the CONTAINS.

 

[markros]

Can you compare your current approach (with CONTAINS) with the second solution outlined in that blog?

I would be very interested in the statistics results.