November 20, 2012 at 9:23 am
Hi All,
I have a list of 40 sensitive words that I need to match on a varchar string. I have set up a full text index and using the CONTAINS keyword I have managed to extract the required strings that contain these sensitive words.
The user only wants to see the word used not the full text string. How could I combine the CONTAINS a PATINDEX/CHARINDEX to retrieve just the word used?
Any help would be greatly appreciated, thanks.
Gary
November 20, 2012 at 9:32 am
Could you please provide a little-bit more details? What is your query looks like now?
November 20, 2012 at 9:39 am
Hi,
Can you add some DDL? also provide some data and then the (simplified) code you use for your query.
As a starter for ten, and only thinking aloud, the issue is that of course when you have a match, you don't know at that point which term is the matching term - meaning that you would also have no idea as to which one of your search terms to use in your CHARINDEX function.
You 'could' (but shouldn't I guess) separate out your search terms and put them all in separate queries which you 'unionise' to display a unique result set - if you do that, then of course it will be much easier, because the text will be your search term.
The above also makes me wonder how you would like to deal with rows where the columns matches against more than 1 of your terms? e.g. both on 'RED' and 'CAR' - would your user want to see only those two words? or just the first match? and would that HAVE to be on a single row? etc 🙂
B
November 20, 2012 at 9:53 am
I've got an example of this in my snippets; I've added a last example at the end, which returns just what i thought you asked for: only the words that are used.
I think this example originally came from someone wanting to search for cuss words; they had a table of 80 items
they wanted to find the words used from their library of offensive phrases, and the find/replace.
try this as an example:
-- Table to hold the words to search for
DECLARE @Patterns TABLE (
search_pattern varchar(50)
)
-- Table to hold the text to search in
DECLARE @test-2 TABLE (
test_text nvarchar(max)
)
-- Populate the test table with the text of the
-- stored procedures in database msdb
INSERT INTO @test-2
SELECT definition
FROM msdb.sys.sql_modules
-- Define some patterns to search for
INSERT INTO @Patterns VALUES ('sysmail')
INSERT INTO @Patterns VALUES ('jobserver')
INSERT INTO @Patterns VALUES ('schedule')
-- Select out the stored procedures that match any of the patterns
SELECT *
FROM @test-2 AS A
WHERE EXISTS (
SELECT 1
FROM @Patterns AS B
WHERE A.test_text LIKE '%' + B.search_pattern + '%')
SELECT B.search_pattern
FROM @test-2 AS A
LEFT OUTER JOIN @Patterns B
ON A.test_text LIKE '%' + B.search_pattern + '%'
GROUP BY B.search_pattern
Lowell
November 21, 2012 at 2:22 am
Many thanks for your help guys. Lowell - I will give this a go now - thanks.
Gary
November 21, 2012 at 2:03 pm
Lowell (11/20/2012)
I've got an example of this in my snippets; I've added a last example at the end, which returns just what i thought you asked for: only the words that are used.I think this example originally came from someone wanting to search for cuss words; they had a table of 80 items
they wanted to find the words used from their library of offensive phrases, and the find/replace.
try this as an example:
-- Table to hold the words to search for
DECLARE @Patterns TABLE (
search_pattern varchar(50)
)
-- Table to hold the text to search in
DECLARE @test-2 TABLE (
test_text nvarchar(max)
)
-- Populate the test table with the text of the
-- stored procedures in database msdb
INSERT INTO @test-2
SELECT definition
FROM msdb.sys.sql_modules
-- Define some patterns to search for
INSERT INTO @Patterns VALUES ('sysmail')
INSERT INTO @Patterns VALUES ('jobserver')
INSERT INTO @Patterns VALUES ('schedule')
-- Select out the stored procedures that match any of the patterns
SELECT *
FROM @test-2 AS A
WHERE EXISTS (
SELECT 1
FROM @Patterns AS B
WHERE A.test_text LIKE '%' + B.search_pattern + '%')
SELECT B.search_pattern
FROM @test-2 AS A
LEFT OUTER JOIN @Patterns B
ON A.test_text LIKE '%' + B.search_pattern + '%'
GROUP BY B.search_pattern
Hey Lowell, I've found that with pattern matching you have listed that you need to slightly modify the incoming string that you're matching like so:
ON A.test_text LIKE '%' + B.search_pattern + '%'
becomes
ON ' ' + A.test_text + ' ' LIKE '%' + B.search_pattern + '%'
otherwise when you search for something like 'Bob' in 'Bob likes cake.' you won't get a result as %Bob% presupposes that there is a character before the letter 'B' in 'Bob likes cake.
Have you run into this before?
Erin
November 21, 2012 at 2:34 pm
Good point Erin; I guess it depends on whether you need to search for whole words or not;
my example was just to show one way to match multiple search terms via LIKE.
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply