Need a select query as part of analytics rule_ids, rule_texts checked in 4k char field

1

declare @SampleRule_table (rule_id int, rule_txt)
insert @SampleRule_table
select 256, 'concerned' union all
select 775, 'atm' union all
select 534, 'customer' union all
select 435, 'daughter' union all
select 531, 'atm' union all
select 538, 'son' union all
select 594, 'fraud'

declare @Sample_long_txt_tbl (account_id int, Text_comments varchar(4000))
insert into @Sample_long_txt_tbl
select 10000124, 'One of the cornerstones of any good fraud prevention strategy is atm the use of data concerned analytics customer, family, son.'

now i want to check rule txt words in table @Sample_long_txt_tbl within 4000 char Text_comments field, if any words appear.

in the above long text atm, concerned, customer & son are there.

Please could you provide a logic with select query joining above two objects and getting below result. where ever the rule_txt is found in the 4k char long text field.

i want to generate result columns as follows , atm has two ids, thats why two rows generated below.:
account_id - rule_id - rule_txt:
10000124 - 256 - concerned
10000124 - 775 - atm
10000124 - 531 - atm
10000124 - 534 - customer
10000124 - 538 - son
10000124 - 594 - fraud

Thank you very much for the helpful detail. thk you.

2 
SELECT A.account_id, B.*
FROM @Sample_long_txt_tbl A
INNER JOIN @SampleRule_table B ON A.text_comments LIKE '%' + B.rule_text + '%'

Note that this will perform a table scan or clustered index scan, regardless of any indexes you have on rule_text or Text_comments. If you have millions of text rows, and/or thousands of rules rows, you will likely see poor performance.

3

hi hope this helps

oops ..saw Robert Volk reply now after doing and posting ( anyhow )
mine is a little different .. NOT doing inner join
performance wise .. mine "May be May be" better

create data script

declare @SampleRule_table table (rule_id int, rule_txt varchar(20))
insert @SampleRule_table
select 256, 'concerned' union all
select 775, 'atm' union all
select 534, 'customer' union all
select 435, 'daughter' union all
select 531, 'atm' union all
select 538, 'son' union all
select 594, 'fraud'
select * from @SampleRule_table

declare @Sample_long_txt_tbl table (account_id int, Text_comments varchar(4000))
insert into @Sample_long_txt_tbl
select 10000124, 'One of the cornerstones of any good fraud prevention strategy is atm the use of data concerned analytics customer, family, son.'
select * from @Sample_long_txt_tbl

SELECT  
     b.account_id
   , a.* 
FROM 
    @SampleRule_table a 
  , @Sample_long_txt_tbl b
WHERE  
    Text_comments like '%'+rule_txt+'%'

image
image866×399 8.82 KB

4

Thank you very much Robert and Harish. I tried using the logic. But one issue i noticed is After the inner join the number of rows are increasing. Not sure why. I have used the join criterias in a proper form.

5

image
image797×610 7.75 KB

6

Both answers produce false positives with the following data. In order to prevent that, you need to split Text_Comments at the word level and join to that.

declare @SampleRule_table TABLE (rule_id int, rule_txt varchar(4000))
insert @SampleRule_table
select 256, 'concerned' union all
select 775, 'atm' union all
select 534, 'customer' union all
select 435, 'daughter' union all
select 531, 'atm' union all
select 538, 'son' union all
select 594, 'fraud'

declare @Sample_long_txt_tbl TABLE (account_id int, Text_comments varchar(4000))
insert into @Sample_long_txt_tbl
VALUES (10000124, 'One of the cornerstones of any good fraud prevention strategy is atm the use of data concerned analytics customer, family, son.')
      ,(10000125, 'Watchout!  It''s Batman!')
      ,(10000126, 'Really? You mean in person?')
;