• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

Check if @input has any value listed in keyword table

Dear all,
I need to modify the answer of this question :
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28344773.html

to check if @input  has any word list in keyword Table ( select TagTitle from NewsSYSTags where isComposedTagKeyword=1 )
then add it to @Keyword Table , also remove all founded keywords from @input  to prevent it from split function.

thanks
0
ethar1
Asked:
ethar1
  • 7
  • 6
1 Solution
 
Surendra NathTechnology LeadCommented:
check if @input  has any word list in keyword Table ( select TagTitle from NewsSYSTags where isComposedTagKeyword=1 )
then add it to @Keyword Table


declare @KeyWords table
(
    word    VARCHAR(1000)
    ,Count  BIGINT
)

declare @input  nVARCHAR(max)
SET @input = 'A quick brown fox jumped over the hedge of your computer in a bussiness rather quick fashion'

select * FROM dbo.split(@input,' ')

;WITH C AS
(
    SELECT COUNT(1) CC,Data FROM dbo.split(@input,' ')
    GROUP BY DATA
)
UPDATE  K
SET     [count] = [count] + CC
FROM    @KeyWords K
JOIN    C
ON      K.word = C.data

;WITH C AS
(
    SELECT COUNT(1) CC,Data FROM dbo.split(@input,' ')
    GROUP BY DATA
)
INSERT INTO @KeyWords
SELECT DATA,CC FROM C
WHERE NOT EXISTS ( SELECT 1 FROM @KeyWords K WHERE K.word = C.data)
AND EXISTS ( SELECT 1 FROM NewsSYSTags S where isComposedTagKeyword=1 and S.TagTitle  = C.data)

SELECT * FROM @KeyWords 

Open in new window


also remove all founded keywords from @input  to prevent it from split function.

This will become a cumbersome process and will hit the performance than improving the performance...
0
 
ethar1Author Commented:
Dear Surendra Ganti, I don't understand your response.
let's divided to 2 parts
first I need to
 insert all key-words in @input founded in keyword here  ( select TagTitle from NewsSYSTags where isComposedTagKeyword=1 )
0
 
Surendra NathTechnology LeadCommented:
thats correct that is exactly how above script works.

and regarding the second requirement of yours, I cannot give you a simple solution without knowing the volumes.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ethar1Author Commented:
insert all key-words in @input founded in  ( select TagTitle from NewsSYSTags where isComposedTagKeyword=1 )  to @keyword

should be done before split
0
 
Surendra NathTechnology LeadCommented:
are you looking for something like below???

declare @KeyWords table
(
    word    VARCHAR(1000)
    ,Count  BIGINT
)

INSERT INTO @KeyWords 
(
word,Count
)
select TagTitle,0 from NewsSYSTags where isComposedTagKeyword=1

declare @input  nVARCHAR(max)
SET @input = 'A quick brown fox jumped over the hedge of your computer in a bussiness rather quick fashion'

select * FROM dbo.split(@input,' ')

;WITH C AS
(
    SELECT COUNT(1) CC,Data FROM dbo.split(@input,' ')
    GROUP BY DATA
)
UPDATE  K
SET     [count] = [count] + CC
FROM    @KeyWords K
JOIN    C
ON      K.word = C.data

;WITH C AS
(
    SELECT COUNT(1) CC,Data FROM dbo.split(@input,' ')
    GROUP BY DATA
)
INSERT INTO @KeyWords
SELECT DATA,CC FROM C
WHERE NOT EXISTS ( SELECT 1 FROM @KeyWords K WHERE K.word = C.data)
AND EXISTS ( SELECT 1 FROM NewsSYSTags S where isComposedTagKeyword=1 and S.TagTitle  = C.data)

SELECT * FROM @KeyWords 

Open in new window

0
 
ethar1Author Commented:
we have 2 type of keywords ,
first type defined from a keywords master table

second type generated from split statement


****
for first type we need to search any keyword in @input  listed in master table of keywords
0
 
Surendra NathTechnology LeadCommented:
Ok, rather than we go back and forth

Why dont you take an example, this might help us understand the stuff better.
0
 
ethar1Author Commented:
very simple
I have @input , need to find any word in @input  listed in simple table ( kID, Keyword) and add the founded in another table
0
 
Surendra NathTechnology LeadCommented:
Then why are insisting to do it before spliting the @input into a table, it can also be done after the split?

is there any bussiness reason for that?
0
 
ethar1Author Commented:
caz the keywords may contains 2 or more words
0
 
Surendra NathTechnology LeadCommented:
check this out

declare @KeyWords table
(
    word    VARCHAR(1000)
    ,Count  BIGINT
)

declare @input  nVARCHAR(max)
SET @input = 'A quick brown fox jumped over the hedge of your computer in a bussiness rather quick fashion'

INSERT INTO @KeyWords 
(
word,count
)
select TagTitle,0 from NewsSYSTags 
where isComposedTagKeyword=1
and @input LIKE '%' + TagTitle + '%'

select * FROM dbo.split(@input,' ')

;WITH C AS
(
    SELECT COUNT(1) CC,Data FROM dbo.split(@input,' ')
    GROUP BY DATA
)
UPDATE  K
SET     [count] = [count] + CC
FROM    @KeyWords K
JOIN    C
ON      K.word = C.data

;WITH C AS
(
    SELECT COUNT(1) CC,Data FROM dbo.split(@input,' ')
    GROUP BY DATA
)
INSERT INTO @KeyWords
SELECT DATA,CC FROM C
WHERE NOT EXISTS ( SELECT 1 FROM @KeyWords K WHERE K.word = C.data)

SELECT * FROM @KeyWords 
                                            

Open in new window

0
 
ethar1Author Commented:
Thanks
0
 
ethar1Author Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now