Contains with string parameter

Question

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)

Ananth G · Answer

Dynamic query. you can use like this create table Employee(Emp_No int ,Emp_Name varchar (20),Emp_DeptNo varchar ( max )) insert Employee select 1, 'A' , '1,2,3,5' ; insert Employee select 2, 'B' , '2' ; insert Employee select 3, '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.String in ( '3' , '5' );

Ananth G · Answer

Run below function CREATE FUNCTION SplitString ( @Input NVARCHAR( MAX ), @ Character CHAR (1) ) RETURNS @ Output TABLE ( ItemNVARCHAR(1000) ) AS BEGIN DECLARE @StartIndex INT ,@EndIndex INT SET @StartIndex=1 IF SUBSTRING (@Input,LEN(@Input)-1,LEN(@Input)) @ Character BEGIN SET @Input=@Input+@ Character END WHILECHARINDEX(@ Character ,@Input)>0 BEGIN SET @EndIndex=CHARINDEX(@ Character ,@Input) INSERT INTO @ Output (Item) SELECT SUBSTRING (@Input,@StartIndex,@EndIndex-1) SET @Input= SUBSTRING (@Input,@EndIndex+1,LEN(@Input)) END RETURN END GO u can like below format SELECT Item FROM dbo.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 SELECT FirstName,LastName FROM Employees WHERE EmployeeId IN ( SELECT CAST (Item AS INTEGER ) FROM dbo.SplitString(@EmployeeIds, ',' ) )

Nitin Sontakke · Answer

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

samanta · Answer

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?

Nitin Sontakke · Answer

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

samanta · Answer

Hi Sir, @SKILL_SET = 'asp,c#,net' @TEMP_SKILLS with AND or OR like 'asp or c# and net' CONTAINS( @SKILL_SET ,@TEMP_SKILLS)

Nitin Sontakke · Answer

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.

Contains with string parameter

Answers (7)