May 25, 2012 at 6:45 am
I have a requirement that if there is a title i.e. "!@#$%^&*()((*(() <>"
it should be searched by any character in fulltext search. I used following code to achieve this but it does not search that title from my Table. I have configured full text properly. If I search a word video then it retuns me rows but special character title is not searching.
declare @SearchText varchar(100) = '!@#$%^&*()((*(() <>'
declare @isExactSearch bit
set @isExactSearch = 0
set @isExactSearch = patindex('"%"', @SearchText)
if @isExactSearch = 1
begin
set @SearchText = '(' + @SearchText + ')'
end
else
begin
set @SearchText = '("' + REPLACE(REPLACE(@SearchText, '"',''), ' ','*") OR ("') + '*")';
end
--SET @SearchText = 'FORMSOF (INFLECTIONAL,'+ @SearchText +')';
SELECT [MashupInfo_Id], MAX(KEY_TBL.RANK) As [Rank]
FROM MashupSearch AS FT_MashupInfo WITH (NOLOCK)
INNER JOIN -- FREETEXTTABLE(MashupSearch,MashupInfo_Title,@SearchText) AS KEY_TBL ON FT_MashupMetaInfo.ID = KEY_TBL.
CONTAINSTABLE(MashupSearch, (MashupInfo_Title) , @SearchText ) AS KEY_TBL ON FT_MashupInfo.ID = KEY_TBL.
GROUP BY
[MashupInfo_Id]
Is this possible to search such title, if yes then How?
Shamshad Ali
May 26, 2012 at 9:02 am
It appears you are expecting patindex to return a bit.
According to
http://msdn.microsoft.com/en-us/library/ms188395.aspx
The return code is
"bigint if expression is of the varchar(max) or nvarchar(max) data types; otherwise int."
May 26, 2012 at 9:52 pm
Test your code in small, simple pieces first.
Read the documentation carefully.
-- This is what you are doing, and it won't work.
select PATINDEX('"!"', '!@#$%^&*()((*(() <>')
RETURNS 0
-- You need to use the SQL wildcard - this will work.
select PATINDEX('%!%', 'AAA!AAA');
RETURNS 4
-- But how do you search for '%' if it is also a wildcard? Use [].
select PATINDEX('%[%]%', 'AAA%AAA');
RETURNS 4
Let me know if you need more help.
- victor
May 30, 2012 at 12:28 am
I am NOT concerned with PatIndex issue, I just used the sample code here for everyone to understand how I am looking for search using this piece of code which bring results from fulltext search... I am trying to find suchs titles that have special character and return me results. Plz. first understand the question I wrote earlier.
Shamshad Ali.
May 31, 2012 at 3:14 am
I used following code to achieve this...
Meaning, of course, that you did NOT use the code.
May 31, 2012 at 5:13 am
Think positive, I am using this code and for explaining my problem I wrote it as it is. so that others can understand what is my question. Try to help me solve if you can otherwise don't reply.
Thanks for understanding.
Shamshad Ali.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply