combine multiple word into single string value

Question

0.00/5 (No votes)

See more:

I am Using free text search in sql server
my sp is like

SQL

Create procedure [dbo].[spExactSearch]
(
  @Keyword nvarchar(255)
)
AS
 BEGIN
 SELECT
       TenderID
      ,TenderTitle
      ,ProductCatID
      ,P.ValueTypeName as ProductCatName
      ,TenderValue
      ,Location
      ,LastDateForSubmission
FROM TenderDetail TD(NOLOCK)
INNER JOIN
   ValueType P(NOLOCK)
   ON
   TD.ProductCatID=P.ValueTypeID
WHERE CONTAINS((ProductSubCatName), @Keyword)
Order By TD.CreatedDate desc
END

the problem is this is working fine for 1 word but when i am using more than 1 word
then it throws an error and i don't want to use Free Text
can someone suggest something like whatever be the value comes in @keyword free text count it as a single word like if i am Using "Stored Procedure" in place of @keyword then its working fine
thanks for help.....

Posted 2-Oct-13 20:10pm

Vishal Pand3y

Add a Solution

Comments

Anurag Sinha V 3-Oct-13 2:32am

Are you trying to compare one of the columns of the table to the @keyword variable?

Anurag Sinha V 3-Oct-13 2:33am

If yes, then why don't you go for something like "columnname IN ('+@keyword+')'"??

Vishal Pand3y 3-Oct-13 2:47am

yes i am using one column to compare
bt what i need is whatever is the content of @keyword it should be in single string like "@keyword"

thatraja 3-Oct-13 3:10am

Any example inputs & outputs?

Vishal Pand3y 3-Oct-13 3:15am

like with contain i have to use WHERE CONTAINS((ColumnName), '"First Second"')
so how i can convert@Keyword in that Format

Vishal Pand3y 3-Oct-13 3:17am

Either I have to Split Content of @Keyword Like 'First and Second'
or use Something like '"First Second"'

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Padam Agrawal · Accepted Answer · 2013-10-05T04:01:00

Hope helpful,

SQL

--You can store each word with single quote separated by commas into @Keyword variable
--Pass string with single quote ' seperated by , comm

Declare @Keyword varchar(200) = '''First'',''third'',''fifth'''
Declare @Query varchar(500)

set @Query = 'select * from TenderDetail where ProductSubCatName in (' + @Keyword + ')'

exec (@Query)