help with difficult issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

help with difficult issue

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2014-01-08 : 15:56:28

Suppose I have a list of 163 items all comma delimted and I need to then take that list and then check it against each row in a table of comments and to see if any one of those 163 items exists in the comments field. I created some sample data below, but my problem is this has to run against a database of over 150,000 million rows.
If you run the sample data below it will be more clear.
Does this have to involve full text searching or is there an easier way to do this? I've never come across something like this before. Any suggestions would be greatly appreciated.

--sample data

--test table to hold list data
CREATE TABLE #temp ( item VARCHAR(100) )
--insert data
INSERT INTO #temp
VALUES ( 'apple' )
INSERT INTO #temp
VALUES ( 'peach' )
INSERT INTO #temp
VALUES ( 'pear' )
INSERT INTO #temp
VALUES ( 'blueberry' )

--put items in comma separated list
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr + ',', '') + item
FROM #temp
SELECT @listStr item
INTO #x

--create comment table
CREATE TABLE #comments
(
account INT
,comment VARCHAR(MAX)
)
--insert sample data into the comment table
INSERT INTO #comments
VALUES ( 123, 'here is some example text apple' )
INSERT INTO #comments
VALUES ( 124, 'here is some example text pear' )
INSERT INTO #comments
VALUES ( 125, 'here is some example text' )
INSERT INTO #comments
VALUES ( 126, 'here is some example text' )

SELECT *
FROM #x

--need to query the comment table to see if any of the comments have the words that appear in the temp table #x. Those words are
--apple, peach, pear and blueberry. It should only return 2 rows because they match

SELECT *
FROM #comments

DROP TABLE #temp
DROP TABLE #x
DROP TABLE #comments

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-01-09 : 03:57:57

Do you that you need to do like the following?

----------------------------------------------

CREATE TABLE #filteredcomment
(
account INT
,comment VARCHAR(MAX)
)

--split keywords by comma
DECLARE CURSOR_KEYWORD CURSOR FOR
WITH WORK(keyword, others) AS (
SELECT
SUBSTRING(item, 0, CHARINDEX(',', item, 0)),
SUBSTRING(item, CHARINDEX(',', item, 0) + 1, LEN(item) - CHARINDEX(',', item, 0)) + ','
FROM #x
UNION ALL
SELECT
SUBSTRING(others, 0, CHARINDEX(',', others, 0)),
SUBSTRING(others, CHARINDEX(',', others, 0) + 1, LEN(others) - CHARINDEX(',', others, 0))
FROM WORK
WHERE 0 < CHARINDEX(',', others, 0)
)
SELECT keyword FROM WORK;

DECLARE @keyword varchar(max);

OPEN CURSOR_KEYWORD;

FETCH NEXT FROM CURSOR_KEYWORD INTO @keyword;

--get part of results
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #filteredcomment
SELECT *
FROM #comments
WHERE comment LIKE '%' + @keyword + '%';

FETCH NEXT FROM CURSOR_KEYWORD INTO @keyword;
END

CLOSE CURSOR_KEYWORD;
DEALLOCATE CURSOR_KEYWORD;

--get results
SELECT *
FROM #filteredcomment;

DROP TABLE #filteredcomment;

From Japan
Sorry, my English ability is limited.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-09 : 05:04:15

quote:
Originally posted by joe8079

Suppose I have a list of 163 items all comma delimted and I need to then take that list and then check it against each row in a table of comments and to see if any one of those 163 items exists in the comments field. I created some sample data below, but my problem is this has to run against a database of over 150,000 million rows.
If you run the sample data below it will be more clear.
Does this have to involve full text searching or is there an easier way to do this? I've never come across something like this before. Any suggestions would be greatly appreciated.

--sample data

--test table to hold list data
CREATE TABLE #temp ( item VARCHAR(100) )
--insert data
INSERT INTO #temp
VALUES ( 'apple' )
INSERT INTO #temp
VALUES ( 'peach' )
INSERT INTO #temp
VALUES ( 'pear' )
INSERT INTO #temp
VALUES ( 'blueberry' )

--put items in comma separated list
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr + ',', '') + item
FROM #temp
SELECT @listStr item
INTO #x

--create comment table
CREATE TABLE #comments
(
account INT
,comment VARCHAR(MAX)
)
--insert sample data into the comment table
INSERT INTO #comments
VALUES ( 123, 'here is some example text apple' )
INSERT INTO #comments
VALUES ( 124, 'here is some example text pear' )
INSERT INTO #comments
VALUES ( 125, 'here is some example text' )
INSERT INTO #comments
VALUES ( 126, 'here is some example text' )

SELECT *
FROM #x

--need to query the comment table to see if any of the comments have the words that appear in the temp table #x. Those words are
--apple, peach, pear and blueberry. It should only return 2 rows because they match

SELECT *
FROM #comments

DROP TABLE #temp
DROP TABLE #x
DROP TABLE #comments

you've to either use Full Text Search or need to use pattern searching using LIKE. FTS would be better from performance perspective I guess as LIKE seraches using wildcard characters will not be able to benefit much from indexes even if present on column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

joe8079
Posting Yak Master

127 Posts

Posted - 2014-01-09 : 08:23:35

Thanks nagino, your solution definitely works. I'm not sure how the while loop while will perform but I'm definitely going to give it a shot.
visakh16, FTS is something i'm not at all familiar with. Would you know how I would go about implementing that solution? Thanks I appreciate all your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-09 : 09:16:59

quote:
Originally posted by joe8079

Thanks nagino, your solution definitely works. I'm not sure how the while loop while will perform but I'm definitely going to give it a shot.
visakh16, FTS is something i'm not at all familiar with. Would you know how I would go about implementing that solution? Thanks I appreciate all your help

https://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources