locked
Need help with 'contains' in fulltext search RRS feed

  • Question

  • hello Forum,

    I have a table which holds large amount of sql statements - 1 per column, row.

    I need to find rows which contain schema name 'code'.

    So i am looking for strings ' code.' or ' code].'

    How would I construct the contains clause?

    I tried below - it produces wrong results. 

    where

    contains(sql_text, 'code..')

    gene

    Wednesday, May 20, 2015 8:42 PM

All replies

  • Hello,

    What exactly do you mean with "wrong result"?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, May 21, 2015 10:33 AM
  • WHERE CONTAINS( SQL_Text, 'Code') should get you results for every description where either code. or code]. appears. Can you provide more information on "wrong results"



    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Thursday, May 21, 2015 11:17 AM
  • Hello Manu, Olaf,

    I will try to state the problem 1 more time.

    I have a table from mdw with sql_text column which holds every sql string for execution for the last month.

    I need to find all sql's which use db. schema 'code'. The word 'code' can be:

    a. used with other table schemas.

    b. be a part of the predicate. For instance: where column1 = 'code'.

    When I run: select sql_text from my_mdw_table where contains(sql_text, 'code'),

    I am getting 1000's of rows which do include a & b .

    However, I think I found the answer:

    select top 10 sql_text from dbo.notable_query_text where contains(sql_text, 'code') and source_id in(7,8,9)

    and

    sql_text like '% [code[\]]'

    Thank you for your respond.


    gene

    Thursday, May 21, 2015 1:52 PM