Problem search colum of type text
-
Hi,
I am trying to write a Stored Procedure that will (among other things)
perform a search for specified words in a column of type text. I get the
following error message:
Microsoft OLE DB Provider for SQL Server error '80040e21'
The text, ntext, and image data types cannot be compared or sorted, except
when using IS NULL or LIKE operator. The line that is causing the problem is:
and exists (select WORD from #SearchWords where (HR_DESC like ''%'' + #SearchWords.WORD + ''%''))
I use an identical line to search a VarChar column and that works fine, the
rest of the procedure works fine if I comment this part out. I have set up
Full-Text Indexing on the relevant column as I picked up some suggestion that
this might be required but it hasn't made any difference.
The thing that I really don't understand is that I am using the 'like'
operator so according to the error it should work.
I'd be grateful for any help or suggestions on this.
-
SQL Server 2000 Full-text Search (FTS) works specificly with columns defined with the TEXT datatype. While both T-SQL LIKE vs. FTS are fuctionally used to search text, the approch and the results can be different as T-SQL LIKE is a pattern based search method, while FTS is a language-specific word based method and results can be senstive to the OS-supplied word brearker. Could you post the output of SELECT @@version as well as your CONTAINS or FREETEXT query? Did you get any results or just not what you were expecting?
As for using T-SQL LIKE with dynamicly built SQL code, see
SELECT @sql = @sql + ' AND c.CompanyName LIKE @xcustname + ''%'''
from: http://www.sommarskog.se/dynsearch/search_orders_1.sp and for more info on building dynamic SQL, see: http://www.sommarskog.se/dyn-search.html
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
John T. Kane -
Hi John, thank you for your reply. I think from what you say that simple pattern matching rather than full-text indexing should suffice. The stored procedure is to take search criteria from a web form, everything else works apart from this one line. I tried writing a select statement which used LIKE on this column in Query Analyser and it worked so I know it can be done. As I said anything else works but this which just returns the error I quoted and no results.
The full text of the procedure is as follows;
CREATE Procedure HOSWEB_GetAccList_XML
(
@NUMWORDS integer,
@SEARCHTEXT varchar(50),
@AREA integer,
@PRICE varchar(5),
@SUBCAT integer,
@TYPE integer,
@FACILITIES varchar(100)
)
as
declare @sql varchar(5000)
----------- if text search is by more than one word, put words into a temporary table ----------------------------
if @NUMWORDS != 0
begin
set @sql = 'declare @COUNTER INT, @WORD VARCHAR(50), @TEMPSTRING VARCHAR(50), @LENSTRING INT SELECT @COUNTER = 1 '
/*Create a temporary table to contain words that are to be searched for*/
set @sql = @sql + 'CREATE TABLE #SearchWords (WORD_ID INT IDENTITY UNIQUE, WORD VARCHAR(50) NOT NULL); '
/*Loop through the searchtext string */
set @sql = @sql + 'WHILE (@COUNTER <= ' + cast(@NUMWORDS as varchar) + ') BEGIN '
/*if it is the last word in the string then the seachtext that is left is the final word*/
set @sql = @sql + 'IF (@COUNTER = ' + cast(@NUMWORDS as varchar) + ') SELECT @WORD = rtrim(ltrim(''' + @SEARCHTEXT + ''')) '
set @sql = @sql + 'ELSE BEGIN '
/*retrieve the string that is to the left of the first space occurring in the searchstring*/
set @sql = @sql + 'SELECT @TEMPSTRING = rtrim(ltrim(LEFT(''' + @SEARCHTEXT + ''', CHARINDEX('' '', ''' + @SEARCHTEXT + ''')))) '
/*this string with leading and trailing blanks removed is a word to be searched for*/
set @sql = @sql + 'SELECT @WORD = @TEMPSTRING SELECT @LENSTRING = LEN(@TEMPSTRING) '
/*set the search text to the text left when the word just found has been removed*/
set @sql = @sql + 'SELECT ''' + @SEARCHTEXT + '''= rtrim(LTRIM(RIGHT(''' + @SEARCHTEXT + ''',LEN(''' + @SEARCHTEXT + ''') - @LENSTRING))) END '
/*Add the word to the SearchWords table*/
set @sql = @sql + 'INSERT INTO #SearchWords (WORD) VALUES (@WORD) SELECT @COUNTER = @COUNTER + 1 END '
end
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------- if search includes facilities, put them into a temporary table --------------------------------------------------
if @FACILITIES != ''
begin
if @sql is null
begin
set @sql = 'declare @pos int '
end
else
begin
set @sql = @sql + 'declare @pos int '
end
set @sql = @sql + 'declare @code varchar(10) '
set @sql = @sql + 'declare @FAC2 varchar(100) '
set @sql = @sql + 'declare @CountFac int '
set @sql = @sql + 'set @FAC2 = ''' + @FACILITIES + ''''
set @sql = @sql + 'set @CountFac = 0 '
set @sql = @sql + 'CREATE TABLE #Res (HR_ID INT, COUNTID INT) '
set @sql = @sql + 'CREATE TABLE #Facilities(FAC_ID INT IDENTITY UNIQUE, FAC_CODE INT) '
set @sql = @sql + 'CREATE TABLE #Res2 (HR_ID2 INT) '
set @sql = @sql + 'WHILE charindex(''|'',@FAC2) <> 0 '
set @sql = @sql + 'select @pos = patindex(''%|%'',@FAC2) '
set @sql = @sql + 'select @code = left(@FAC2,@pos - 1) '
set @sql = @sql + 'INSERT INTO #Facilities (FAC_CODE) '
set @sql = @sql + 'VALUES (@code) '
set @sql = @sql + 'set @CountFac = (@CountFac + 1) '
set @sql = @sql + 'select @FAC2 = stuff(@FAC2,1,@pos,'' '') '
set @sql = @sql + 'INSERT INTO #Res (HR_ID, COUNTID) '
set @sql = @sql + '(select HR_ID, COUNT (HR_ID) '
set @sql = @sql + 'from HOS_RES_TO_FAC '
set @sql = @sql + 'join #Facilities '
set @sql = @sql + 'on HOS_RES_TO_FAC.HF_ID = #Facilities.FAC_CODE '
set @sql = @sql + 'group by HR_ID) '
set @sql = @sql + 'INSERT INTO #Res2 '
set @sql = @sql + 'select HR_ID from #Res WHERE COUNTID = @CountFac '
end
----------------------------------------------------------------------------------------------------------------------------------------------------------------
if @sql is null
begin
set @sql = 'select 1 as TAG, '
end
else
begin
set @sql = @sql + 'select 1 as TAG, '
end
set @sql = @sql + 'null as Parent, '
set @sql = @sql + 'HOS_RESOURCES.HR_ID as [RESOURCE!1!HR_ID], '
set @sql = @sql + 'HR_TITLE as [RESOURCE!1!HR_TITLE], '
set @sql = @sql + 'HR_SUBTITLE as [RESOURCE!1!HR_SUBTITLE], '
set @sql = @sql + 'HR_ADDR_VISIBLE as [RESOURCE!1!HR_ADDR_VISIBLE], '
set @sql = @sql + 'HR_ADDRESS3 as [RESOURCE!1!HR_ADDRESS3], '
set @sql = @sql + 'HR_TOWN as [RESOURCE!1!HR_TOWN], '
set @sql = @sql + 'HR_TEL as [RESOURCE!1!HR_TEL], '
set @sql = @sql + 'HR_TEL_VISIBLE as [RESOURCE!1!HR_TEL_VISIBLE], '
set @sql = @sql + 'HR_DESC as [RESOURCE!1!HR_DESC], '
set @sql = @sql + 'HR_PRICE as [RESOURCE!1!HR_PRICE], '
set @sql = @sql + 'HR_OFFERS as [RESOURCE!1!HR_OFFERS], '
set @sql = @sql + 'HR_ETC_STARS as [RESOURCE!1!HR_ETC_STARS], '
set @sql = @sql + 'HR_AA_STARS as [RESOURCE!1!HR_AA_STARS], '
set @sql = @sql + 'HR_RAC_STARS as [RESOURCE!1!HR_RAC_STARS], '
set @sql = @sql + 'HR_RED_STARS as [RESOURCE!1!HR_RED_STARS], '
set @sql = @sql + 'HR_AA_STARS + HR_RAC_STARS + HR_ETC_STARS as [RESOURCE!1!HR_ALL_STARS], '
set @sql = @sql + 'HR_ETC_DIAMONDS as [RESOURCE!1!HR_ETC_DIAMONDS], '
set @sql = @sql + 'HR_AA_DIAMONDS as [RESOURCE!1!HR_AA_DIAMONDS], '
set @sql = @sql + 'HR_RAC_DIAMONDS as [RESOURCE!1!HR_RAC_DIAMONDS], '
set @sql = @sql + 'HR_RED_DIAMONDS as [RESOURCE!1!HR_RED_DIAMONDS], '
set @sql = @sql + 'HR_AA_DIAMONDS + HR_RAC_DIAMONDS + HR_ETC_DIAMONDS as [RESOURCE!1!HR_ALL_DIAMONDS], '
set @sql = @sql + 'HCAT_DESC as [RESOURCE!1!HCAT_DESC], '
set @sql = @sql + 'null as [PHOTO!2!HA_FILENAME], '
set @sql = @sql + 'null as [PHOTO!2!HA_LINKNAME], '
set @sql = @sql + 'null as [PHOTO!2!HA_THUMBNAIL], '
set @sql = @sql + 'null as [FACILITIES!3!HF_DESC], '
set @sql = @sql + 'null as [FACILITIES!3!HF_FILE] '
set @sql = @sql + 'from HOS_RESOURCES '
set @sql = @sql + 'join HOS_CATEGORIES '
set @sql = @sql + 'on HOS_RESOURCES.HR_CAT = HOS_CATEGORIES.HCAT_ID '
--------------------------------------------------------
if @FACILITIES != ''
begin
set @sql = @sql + 'join #Res2 '
set @sql = @sql + 'on HOS_RESOURCES.HR_ID = #Res2.HR_ID2 '
end
--------------------------------------------------------
set @sql = @sql + 'where HOS_CATEGORIES.HCAT_DESC = ''Accommodation'' and HR_SUBCAT = ' + cast(@SUBCAT as varchar) + ' and HR_AUTH = 1 and HR_VISIBLE = 1 '
if @AREA != 0
begin
set @sql = @sql + ' and HR_AREA2 = ' + cast(@AREA as varchar)
end
if @TYPE != 0
begin
set @sql = @sql + ' and HR_TYPE = ' + cast(@TYPE as varchar)
end
if @NUMWORDS != 0
begin
set @sql = @sql + ' and exists (select WORD from #SearchWords where (HR_TITLE like ''%'' + #SearchWords.WORD + ''%''))'
set @sql = @sql + ' and exists (select WORD from #SearchWords where (HR_DESC like ''%'' + #SearchWords.WORD + ''%''))'
end
if @PRICE != ''
begin
---------------------------- Bed and Breakfast ---------------------------------
if @PRICE = 'BB1'
begin
set @sql = @sql + ' and HR_MAX_PRICE <= 25'
end
if @PRICE = 'BB2'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 25 and HR_MAX_PRICE <= 35'
end
if @PRICE = 'BB3'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 35 and HR_MAX_PRICE <= 50'
end
if @PRICE = 'BB4'
begin
set @sql = @sql + ' and HR_MAX_PRICE >= 50'
end
-------------------- Camping and Caravan Sites ------------------------------------------------------------
if @PRICE = 'CC1'
begin
set @sql = @sql + ' and HR_MAX_PRICE <= 5'
end
if @PRICE = 'CC2'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 5 and HR_MAX_PRICE <= 10'
end
if @PRICE = 'CC3'
begin
set @sql = @sql + ' and HR_MAX_PRICE >= 10'
end
-------------------- Hotels and Lodges ----------------------------------------------------
if @PRICE = 'HL1'
begin
set @sql = @sql + ' and HR_MAX_PRICE <= 50'
end
if @PRICE = 'HL2'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 50 and HR_MAX_PRICE <= 75'
end
if @PRICE = 'HL3'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 75 and HR_MAX_PRICE <= 100'
end
if @PRICE = 'HL4'
begin
set @sql = @sql + ' and HR_MAX_PRICE >= 100'
end
------------------------ Self Catering -----------------------------------------------------------------
if @PRICE = 'SC1'
begin
set @sql = @sql + ' and HR_MAX_PRICE <= 250'
end
if @PRICE = 'SC2'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 250 and HR_MAX_PRICE <= 500'
end
if @PRICE = 'SC3'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 500 and HR_MAX_PRICE <= 750'
end
if @PRICE = 'SC4'
begin
set @sql = @sql + ' and HR_MAX_PRICE >= 1000'
end
end
set @sql = @sql + ' UNION ALL '
set @sql = @sql + 'select 2, 1, '
set @sql = @sql + 'HOS_RESOURCES.HR_ID as [RESOURCE!1!HR_ID], '
set @sql = @sql + 'HR_TITLE as [RESOURCE!1!HR_TITLE], '
set @sql = @sql + 'Join1.HA_FILENAME, '
set @sql = @sql + 'Join1.HA_LINKNAME, '
set @sql = @sql + 'Join1.HA_THUMBNAIL, '
set @sql = @sql + 'from HOS_RESOURCES '
set @sql = @sql + 'join HOS_CATEGORIES '
set @sql = @sql + 'on HOS_RESOURCES.HR_CAT = HOS_CATEGORIES.HCAT_ID '
--------------------------------------------------------
if @FACILITIES != ''
begin
set @sql = @sql + 'join #Res2 '
set @sql = @sql + 'on HOS_RESOURCES.HR_ID = #Res2.HR_ID2 '
end
--------------------------------------------------------
set @sql = @sql + 'LEFT join HOS_ADDFILES as Join1 '
set @sql = @sql + 'on HOS_RESOURCES.HR_ID = Join1.HA_RESOURCE_ID '
set @sql = @sql + 'where HOS_CATEGORIES.HCAT_DESC = ''Accommodation'' and HR_SUBCAT = ' + cast(@SUBCAT as varchar) + ' and HR_AUTH = 1 and HR_VISIBLE = 1 '
set @sql = @sql + ' and Join1.HA_MAIN=1 and Join1.HA_VISIBLE=1'
if @AREA != 0
begin
set @sql = @sql + ' and HR_AREA2 = ' + cast(@AREA as varchar)
end
if @TYPE != 0
begin
set @sql = @sql + ' and HR_TYPE = ' + cast(@TYPE as varchar)
end
if @NUMWORDS != 0
begin
set @sql = @sql + ' and exists (select WORD from #SearchWords where (HR_TITLE like ''%'' + #SearchWords.WORD + ''%''))'
set @sql = @sql + ' and exists (select WORD from #SearchWords where (HR_DESC like ''%'' + #SearchWords.WORD + ''%''))'
end
if @PRICE != ''
begin
---------------------------- Bed and Breakfast ---------------------------------
if @PRICE = 'BB1'
begin
set @sql = @sql + ' and HR_MAX_PRICE <= 25'
end
if @PRICE = 'BB2'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 25 and HR_MAX_PRICE <= 35'
end
if @PRICE = 'BB3'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 35 and HR_MAX_PRICE <= 50'
end
if @PRICE = 'BB4'
begin
set @sql = @sql + ' and HR_MAX_PRICE >= 50'
end
-------------------- Camping and Caravan Sites ------------------------------------------------------------
if @PRICE = 'CC1'
begin
set @sql = @sql + ' and HR_MAX_PRICE <= 5'
end
if @PRICE = 'CC2'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 5 and HR_MAX_PRICE <= 10'
end
if @PRICE = 'CC3'
begin
set @sql = @sql + ' and HR_MAX_PRICE >= 10'
end
-------------------- Hotels and Lodges ----------------------------------------------------
if @PRICE = 'HL1'
begin
set @sql = @sql + ' and HR_MAX_PRICE <= 50'
end
if @PRICE = 'HL2'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 50 and HR_MAX_PRICE <= 75'
end
if @PRICE = 'HL3'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 75 and HR_MAX_PRICE <= 100'
end
if @PRICE = 'HL4'
begin
set @sql = @sql + ' and HR_MAX_PRICE >= 100'
end
------------------------ Self Catering -----------------------------------------------------------------
if @PRICE = 'SC1'
begin
set @sql = @sql + ' and HR_MAX_PRICE <= 250'
end
if @PRICE = 'SC2'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 250 and HR_MAX_PRICE <= 500'
end
if @PRICE = 'SC3'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 500 and HR_MAX_PRICE <= 750'
end
if @PRICE = 'SC4'
begin
set @sql = @sql + ' and HR_MAX_PRICE >= 1000'
end
end
set @sql = @sql + ' UNION ALL '
set @sql = @sql + 'select 3, 1, '
set @sql = @sql + 'HOS_RESOURCES.HR_ID as [RESOURCE!1!HR_ID], '
set @sql = @sql + 'HR_TITLE as [RESOURCE!1!HR_TITLE], '
set @sql = @sql + 'Join3.HF_DESC, '
set @sql = @sql + 'Join3.HF_FILE '
set @sql = @sql + 'from HOS_RESOURCES '
set @sql = @sql + 'join HOS_CATEGORIES '
set @sql = @sql + 'on HOS_RESOURCES.HR_CAT = HOS_CATEGORIES.HCAT_ID '
--------------------------------------------------------
if @FACILITIES != ''
begin
set @sql = @sql + 'join #Res2 '
set @sql = @sql + 'on HOS_RESOURCES.HR_ID = #Res2.HR_ID2 '
end
--------------------------------------------------------
set @sql = @sql + 'left join HOS_RES_TO_FAC as Join2 '
set @sql = @sql + 'on HOS_RESOURCES.HR_ID = Join2.HR_ID '
set @sql = @sql + 'left join HOS_FACILITIES AS Join3 '
set @sql = @sql + 'on Join2.HF_ID = Join3.HF_ID '
if @FACILITIES != ''
begin
set @sql = @sql + 'left join #Facilities as Join4 '
set @sql = @sql + 'on Join3.HF_ID = Join4.FAC_ID '
end
set @sql = @sql + 'where HOS_CATEGORIES.HCAT_DESC = ''Accommodation'' and HR_SUBCAT = ' + cast(@SUBCAT as varchar) + 'and HR_AUTH = 1 and HR_VISIBLE = 1 '
if @AREA != 0
begin
set @sql = @sql + ' and HR_AREA2 = ' + cast(@AREA as varchar)
end
if @TYPE != 0
begin
set @sql = @sql + ' and HR_TYPE = ' + cast(@TYPE as varchar)
end
if @NUMWORDS != 0
begin
set @sql = @sql + ' and exists (select WORD from #SearchWords where (HR_TITLE like ''%'' + #SearchWords.WORD + ''%''))'
set @sql = @sql + ' and exists (select WORD from #SearchWords where (HR_DESC like ''%'' + #SearchWords.WORD + ''%''))'
end
if @PRICE != ''
begin
---------------------------- Bed and Breakfast ---------------------------------
if @PRICE = 'BB1'
begin
set @sql = @sql + ' and HR_MAX_PRICE <= 25'
end
if @PRICE = 'BB2'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 25 and HR_MAX_PRICE <= 35'
end
if @PRICE = 'BB3'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 35 and HR_MAX_PRICE <= 50'
end
if @PRICE = 'BB4'
begin
set @sql = @sql + ' and HR_MAX_PRICE >= 50'
end
-------------------- Camping and Caravan Sites ------------------------------------------------------------
if @PRICE = 'CC1'
begin
set @sql = @sql + ' and HR_MAX_PRICE <= 5'
end
if @PRICE = 'CC2'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 5 and HR_MAX_PRICE <= 10'
end
if @PRICE = 'CC3'
begin
set @sql = @sql + ' and HR_MAX_PRICE >= 10'
end
-------------------- Hotels and Lodges ----------------------------------------------------
if @PRICE = 'HL1'
begin
set @sql = @sql + ' and HR_MAX_PRICE <= 50'
end
if @PRICE = 'HL2'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 50 and HR_MAX_PRICE <= 75'
end
if @PRICE = 'HL3'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 75 and HR_MAX_PRICE <= 100'
end
if @PRICE = 'HL4'
begin
set @sql = @sql + ' and HR_MAX_PRICE >= 100'
end
------------------------ Self Catering -----------------------------------------------------------------
if @PRICE = 'SC1'
begin
set @sql = @sql + ' and HR_MAX_PRICE <= 250'
end
if @PRICE = 'SC2'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 250 and HR_MAX_PRICE <= 500'
end
if @PRICE = 'SC3'
begin
set @sql = @sql + ' and HR_MIN_PRICE >= 500 and HR_MAX_PRICE <= 750'
end
if @PRICE = 'SC4'
begin
set @sql = @sql + ' and HR_MAX_PRICE >= 1000'
end
end
set @sql = @sql + ' order by [RESOURCE!1!HR_TITLE], [tag], [FACILITIES!3!HF_DESC] '
set @sql = @sql + 'for xml explicit'
Execute(@SQL)
return
GO
-
You're welcome, Conprog,
No, I'm only meant to say that you would get different results from using T-SQL LIKE (pattern search algorithm) vs. CONTAINS or FREETEXT as these use language-specific linguistic word-based search algorithms.
So, all the SQL code executes successfully, except for the following code block:
if @NUMWORDS != 0
begin
set @sql = @sql + ' and exists (select WORD from #SearchWords where (HR_TITLE like ''%'' + #SearchWords.WORD + ''%''))'
set @sql = @sql + ' and exists (select WORD from #SearchWords where (HR_DESC like ''%'' + #SearchWords.WORD + ''%''))'
end
Your search_string valiable is in fact a temp table column "#SearchWords.WORD", when you get to this part of your stored proc. Could you use an @variable or table variable instead of directly referencing this temp table column? Is the column "#SearchWords.WORD" nullable?
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
John T. Kane -
Hello again John,
I see what you you mean now about the results.
The odd thing about the code is that the first line of that section;
set @sql = @sql + ' and exists (select WORD from #SearchWords where (HR_TITLE like ''%'' + #SearchWords.WORD + ''%''))'
works fine but then HR_TITLE is a varchar column, whereas HR_DESC is a text column so it's something to do with that type (I assume) but its very odd that I can get a valid result set back if I just do;
select HR_DESC
from HOS_RESOURCES
where HR_DESC like '%test%'
you evidentally can search HR_DESC this way even tho' the error I get from running the SP (and a Google search) suggests otherwise. Somewhere I read that if the contents of the text column was under a certain size (can't remember exactly but I think our data is) then it would automatically treat it as a varchar. I have tried doing a cast to change it to varchar but that didn't make any difference.
As you probably realise the idea is that you can pass in a string of words and they will be cut up and stored in #SearchWords, as such the column WORD is not allowed to have nulls which is specified on creating the table. I could assign the value to a variable - I also ran my test like this but I can't see why it would help ... but then what would I know
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply