Contains with string parameter

samanta

10y966

Hi All,

It is possible to pass string as contains parameter.

Here i want to pass @SKILL_SET value for contains.

DECLARE @SKILL_SET varchar(500)='asp,c#,net'.

CONTAINS(@SKILL_SET,@TEMP_SKILLS)

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ananth G10y ago
Dynamic query. you can use like this
create table Employee(Emp_Noint,Emp_Name varchar(20),Emp_DeptNo varchar(max))
insert Employee select1,'A','1,2,3,5';
insert Employee select2,'B','2';
insert Employee select3,'C','4,5';
Output Query:
select distinct temp1.Emp_No,temp1.Emp_Name,temp1.Emp_DeptNo from(SELECT A.Emp_No,A.Emp_Name,A.Emp_DeptNo,
Split.a.value('.','VARCHAR(100)')AS String
FROM(SELECT Emp_No,Emp_Name,Emp_DeptNo,
CAST(''+REPLACE([Emp_DeptNo],',','')+''AS XML)AS String
FROM Employee)AS A CROSS APPLY String.nodes('/M')AS Split(a))temp1 where temp1.Stringin('3','5');
0
Nitin Sontakke10y ago
Honestly, I don't know what might be going on under the hood. When I googled, i found these two related links which might be useful. But from whatever you described, I guessed that it is to do with the time it takes to refresh the full text search index, which doesn't happen immediately.

http://stackoverflow.com/questions/14264375/how-to-refresh-a-full-text-index-from-within-a-sql-server-transaction

http://stackoverflow.com/questions/3548590/how-do-i-force-a-refresh-of-a-fulltext-index-within-a-transaction-in-mssql/14246018#14246018
0
Ananth G10y ago
Run below function
CREATE FUNCTIONSplitString
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS@OutputTABLE(
ItemNVARCHAR(1000)
)
AS
BEGIN
DECLARE@StartIndexINT,@EndIndexINT
SET@StartIndex=1
IFSUBSTRING(@Input,LEN(@Input)-1,LEN(@Input))<>@Character
BEGIN
SET@Input=@Input+@Character
END
WHILECHARINDEX(@Character,@Input)>0
BEGIN
SET@EndIndex=CHARINDEX(@Character,@Input)
INSERTINTO@Output(Item)
SELECTSUBSTRING(@Input,@StartIndex,@EndIndex-1)
SET@Input=SUBSTRING(@Input,@EndIndex+1,LEN(@Input))
END
RETURN
END
GO
u can like below format
SELECTItem
FROMdbo.SplitString('Apple,Mango,Banana,Guava',',')
That function also use your table
can you refer this link
http://www.aspsnippets.com/Articles/Split-function-in-SQL-Server-Example-Function-to-Split-Comma-separated-Delimited-string-in-SQL-Server-2005-2008-and-2012.aspx
SELECTFirstName,LastName
FROMEmployees
WHEREEmployeeIdIN(
SELECTCAST(ItemASINTEGER)
FROMdbo.SplitString(@EmployeeIds,',')
)
0
samanta10y ago
Sir,
I have a situation like i want to update full texted column with some data and
than i am using same column for contains but its not working.
For example
update mytable set SKILL_SET='asp,net' where condition.(SKILL_SET is fulltext indexed)
declare @TEMP_SKILLS varchar(500)='asp or c# and net'
than i an trying to select data like
select 1 from mytable where CONTAINS(SKILL_SET,@TEMP_SKILLS) where condition
in this case i am getting output as 0 .
if i run query more than once it is giving output as 1.
Can i know what is wrong in this?
0
Nitin Sontakke10y ago
Looks like there is some confusion. Contains is a Full Text Search feature and cannot be used as having both parameters as variables.
The first parameter must be a column from table on which full text search is enabled by creating full text search index.
SQL server maintains the data in separate indexes and returns true or false depending on index data and second parameter you sent with data and conditions.
The complete reference can be found herehttps://msdn.microsoft.com/en-us/library/ms187787.aspx
0
samanta10y ago
Hi Sir,
@SKILL_SET = 'asp,c#,net'
@TEMP_SKILLSwith AND or OR like 'asp or c# and net'
CONTAINS(@SKILL_SET,@TEMP_SKILLS)
0
Nitin Sontakke10y ago
What should be the condition between words, AND or OR? Depending on that , you can have "ASP AND C# AND NET", etc.

Then probably it is okay to have as you suggested. But I am not too sure. I believe function splits the string on the basis of white-space (missing from your example string) and you should also tell if you want all the words or any one is fine by AND or OR.
0