none
Find exact word but not matching words with T SQL RRS feed

  • Question

  • HI, i ran a SQL profiler trace to find usage of Account table and in Trace definition column filters I used text data like '%Account%' and i am getting tons of data as there are 4 more tables  matching Account= AccountH, AccountRT, AccountStatus, AccountStatusRT,

    column filters  like 'Account'.-i used this to get only account but trace did not capture what i needed.


    My trace file is huge now as i only want to find who is using table Account only not all other tables matching Account

     where CAST(textdata as varchar (max) ) like  '%Account% ' 
     and   CAST(textdata as varchar (max) ) <>'AccountH' 

     and   CAST(textdata as varchar (max) )<>'AccountRT' 

     and   CAST(textdata as varchar (max) ) <> 'AccountStatus' 

     and   CAST(textdata as varchar (max) ) <>'AccountStatusRT' 

     Can some one please guide me.

    Thanks, 

    Thursday, January 2, 2020 3:50 PM

Answers

  • Consider the next condition too:

       text like '%[^a-z0-9]Account[^a-z0-9]%'

    or text like '%[^a-z0-9]Account'

    or text like 'Account[^a-z0-9]%'

    or text = 'Account'

     



    • Edited by Viorel_MVP Thursday, January 2, 2020 8:09 PM
    • Marked as answer by coolguy123SQL Friday, January 3, 2020 6:55 PM
    Thursday, January 2, 2020 8:08 PM

All replies

  • where CAST(textdata as varchar (max) ) like  '%Account % ' 
    Thursday, January 2, 2020 3:55 PM
    Moderator
  •  where CAST(textdata as varchar (max)) like '%Account % '
     and   CAST(textdata as varchar (max)) not like '%AccountH %'
     and   CAST(textdata as varchar (max)) not like '%AccountRT %'
     and   CAST(textdata as varchar (max)) not like '%AccountStatus %'
     and   CAST(textdata as varchar (max)) not like '%AccountStatusRT %'

    A Fan of SSIS, SSRS and SSAS

    Thursday, January 2, 2020 4:12 PM
  • no, that would still get AccountH in below example

    where CAST(textdata as varchar (max)) like '%Account % '
     and   CAST(textdata as varchar (max)) not like '%AccountH %'

    Thursday, January 2, 2020 4:56 PM
  • no, that would still get AccountH in below example

    where CAST(textdata as varchar (max)) like '%Account % '
     and   CAST(textdata as varchar (max)) not like '%AccountH %'

    create table test (textdata  text)
    
    insert into test values ('this is Account only ')
    ,('this is AccountH')
    ,('this is AccountRT')
    
    select * from test 
      WHERE CAST(textdata as varchar (max) ) like  '%Account % ' 
    
    --Result
    --this is Account only 
    
    drop table test


    Thursday, January 2, 2020 5:23 PM
    Moderator
  • Consider the next condition too:

       text like '%[^a-z0-9]Account[^a-z0-9]%'

    or text like '%[^a-z0-9]Account'

    or text like 'Account[^a-z0-9]%'

    or text = 'Account'

     



    • Edited by Viorel_MVP Thursday, January 2, 2020 8:09 PM
    • Marked as answer by coolguy123SQL Friday, January 3, 2020 6:55 PM
    Thursday, January 2, 2020 8:08 PM
  • Hi coolguy123SQL,

    --It will be more intuitive if you post some sample data

    create table #t(textdata text)

    insert #t values ('xxx Account xxx'), ('xxx AccountH xxx'), ('xxx AccountRT xxx'), ('xxx AccountStatus xxx'), ('xxx AccountStatusRT xxx') select * from #t where CAST(textdata as varchar(max)) like '%Account%' and CAST(textdata as varchar(max)) not like '%AccountH%' and CAST(textdata as varchar(max)) not like '%AccountRT%' and CAST(textdata as varchar(max)) not like '%AccountStatus%' and CAST(textdata as varchar(max)) not like '%AccountStatusRT%' /* textdata ----------------- xxx Account xxx */


    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, January 3, 2020 3:04 AM
  • Viorel, you the champ.

    Worked PERFECTLY.

    Thanks a lot

    Friday, January 3, 2020 6:55 PM