SQL Server Trying to search for needles in haystacks using dynamic queries

ut6juiuv  于 2023-10-15  发布在  其他
关注(0)|答案(4)|浏览(137)

Please consider the following SQL Fiddle: http://sqlfiddle.com/#!3/9d4fc/3

I have 3 documents:

('id1', 'lorem ipsum dolor dog'),
('id2', 'the cat jumps over the lazy dog'),
('id3', 'i have no clue what to write');

I want to dynamically search these documents for needles:

('a', 'dog'),
('b', 'dolor'),
('c', 'write');

The results I need would be table tmp_auditlog_results looking like

doc   needle  string
---------------------------------------------
id1   dog     lorem ipsum dolor dog
id2   dog     the cat jumps over the lazy dog
id1   dolor   lorem ipsum dolor dog
id3   write   i have no clue what to write

I got confused by the dynamic query adding to the result table. Could you please have a look how I can get this result from the result I have now? Any help would be appreciated.

olmpazwi

olmpazwi1#

You don't need any dynamic code. You can use CHARINDEX

INSERT INTO tmp_auditlog_results
SELECT S.docid, F.NEEDLE 
FROM tmp_auditlog_subselection AS S
CROSS JOIN tmp_auditlog_fields AS F
WHERE CHARINDEX(F.needle, S.haystack) != 0
jmp7cifd

jmp7cifd2#

Replace the select statement:

select * from tmp_auditlog_fields;

with following statement:

SELECT DISTINCT 'id' + CAST(A.id as VARCHAR(9999)) AS doc, needle,  B.haystack 
FROM tmp_auditlog_fields A
INNER JOIN tmp_auditlog_subselection B 
ON B.docid =  'id'+ CAST(A.id as VARCHAR(9999))
x6yk4ghg

x6yk4ghg3#

The answer from podiluska needed some renaming of column names. So i created this example starting from his query

Given these Tables

CREATE TABLE Haystacks (
  docid varchar(255),
  haystack varchar(255)
)
CREATE TABLE Needles (
  id int identity(1,1),
  fieldid varchar(255),
  needle varchar(255)
)

And these records

insert into Haystacks (docid, haystack) values
  ('id1', 'lorem ipsum dolor dog'),
  ('id2', 'the cat jumps over the lazy dog'),
  ('id3', 'i have no clue what to write');

insert into Needles (fieldid, needle) values
  ('a', 'dog'),
  ('b', 'dolor'),
  ('c', 'write');

This query

SELECT docid, needles.needle, haystack 
FROM Haystacks
INNER JOIN Needles ON Haystacks.haystack 
like '%'+Needles.needle+'%'

returns this result

See this demo fiddle this matches the expected query result

doc   needle  string
    ---------------------------------------------
    id1   dog     lorem ipsum dolor dog
    id2   dog     the cat jumps over the lazy dog
    id1   dolor   lorem ipsum dolor dog
    id3   write   i have no clue what to write
92vpleto

92vpleto4#

INSERT tmp_auditlog_results (doc,needle,string)
    SELECT doc, needles.needle, string 
    FROM  haystacks
    INNER JOIN needles ON haystacks.string like '%'+needles.needle+'%'

相关问题