October 9, 2006 at 9:35 am
Doing a SQL word search that takes in @SearchTerms as a varchar.
The WHILE loop works great with up to 37 words in @Searchterms but falls over when a 38th word is added.
I do have a workaround (not allow so many words in search)
But would like to know just what is going wrong here.
Thanks in Advance
------------------- Now the words can be counted -------------------------------
set @wordcount = dbo.wordcount(@SearchTerms) -- a dependency -- code should be substituted
select @wordcount as wordcount
-----------------------------------------------------------------------------------
set @MySQL2 ='Some text here '
while @wordcount > 0
begin
if @wordcount > 1
begin
set @SpaceAt=charindex(' ', @Searchterms)
set @TheWord = (SELECT LEFT(@SearchTerms,@SpaceAt-1))-- this falls over at the 38th word
end
else set @theword = @Searchterms -- only one word left
-- select( @theword + char(14))as theword,@wordcount as wordcount -- un rem for debugging
set @MySQL2 = @MySQL2 + @TheWord + ' '
-- more word(s) add the ANDorOR and get rid of the first word
if @wordcount > 1
begin
set @MySQL2 = @MySQL2+ @ANDorOR
set @searchterms= replace(@searchterms, SUBSTRING(@Searchterms,1,@Spaceat),'')
set @searchterms= ltrim(@searchterms)
end
set @wordcount=@wordcount -1
end
select @mysql2 as TextSearch -- remove this line
October 9, 2006 at 12:31 pm
What is the error message you are getting?
October 9, 2006 at 1:13 pm
Can you post the function definition for your dbo.wordcount function? Also, post an example of a function call that does not produce the results you are expecting.
October 10, 2006 at 1:59 am
Thanks guys - I have prepared a stripped down version of the original sql that uses the pubs database to test with - it works when the @SearchTerms is as follows
set @SearchTerms = 'aaa b c d e f g h i j k l m n o p q r s t u v w x y z 1 2 3 4 5 6 7 8 9 10 11' --37 words in this string
However it falls over when you add one more word as follows
set @SearchTerms = 'aaa b c d e f g h i j k l m n o p q r s t u v w x y z 1 2 3 4 5 6 7 8 9 10 11 12' --38 words in this string
Producing the following error message:
Server: Msg 536, Level 16, State 3, Line 27
Invalid length parameter passed to the substring function.
I cant understand why it works up to 37 words and fails on the 38th or more. Here is the code for the UDF wordcount which works well followed by the main code which will run in QA(using pubs)
------------------------------------------------------------------------
CREATE FUNCTION [dbo].[WordCount] ( @InputString VARCHAR(4000) )
RETURNS INT
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @WordCount INT
SET @Index = 1
SET @WordCount = 0
WHILE @Index 0
begin
if @wordcount > 1
begin
set @SpaceAt=charindex(' ', @Searchterms)
set @TheWord = (SELECT LEFT(@SearchTerms,@SpaceAt-1))
end
else set @theword = @Searchterms -- only one word left
--select( @theword + char(14))as theword,@wordcount as wordcount -- un rem for debugging
set @MySQL2 = @MySQL2 + @TheWord +' '
-- more word(s) add the ANDorOR and get rid of the first word
if @wordcount > 1
begin
set @MySQL2 = @MySQL2+ @ANDorOR
set @searchterms= replace(@searchterms, SUBSTRING(@Searchterms,1,@Spaceat),'')
set @searchterms= ltrim(@searchterms)
end
set @wordcount=@wordcount -1
end
select @mysql2 as TextSearch -- remove this line
October 10, 2006 at 3:09 am
Ok I can see the flaw - its the replace function that causes the problem
set @searchterms= replace(@searchterms, SUBSTRING(@Searchterms,1,@Spaceat),'')
when it removes '1 ' it affects '11 ' in the searchterms etc
Need to change the logic to drop the first part of searchterms or select Right of Searchterms from @SpaceAt
thanks for looking
October 10, 2006 at 4:01 am
You can use
set @searchterms = SUBSTRING(@Searchterms,@Spaceat,len((@Searchterms)))
instead of
set @searchterms= replace(@searchterms, SUBSTRING(@Searchterms,1,@Spaceat),'')
--Ramesh
October 10, 2006 at 4:22 am
Thats exactly what I did Ramesh - works a treat now
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply