Solved

Procedure Breaks when using parameter string

Posted on 2014-04-14
1
130 Views
Last Modified: 2014-04-17
I have a procedure that is throwing the error,

 
Invalid length parameter passed to the LEFT or SUBSTRING function.

Open in new window


But If I replace this line

,CHARINDEX(@searchString, Sample2TextColumn2) as SearchPos

Open in new window


With this,

,ISNULL(NULLIF(CHARINDEX(@searchString, Sample2TextColumn2) - 1, -1), LEN(@searchString)) AS SearchPos

Open in new window


Results are returned but the counts are returning 0.

If you resplace the @searchString with the string 'first' <--- to search the sample data, the correct results are returned and counts.

Here is the procedure

;with CTE as 
    (SELECT       
      a.Sample2Id
     ,dbo.StripHTML(Sample2TextColumn2) as Target
     ,CASE When LEN(Sample2TextColumn2) <= 60
             Then Sample2TextColumn2 
             ELSE LEFT(Sample2TextColumn2, 60) + '...'
          END As BeginningOfString
     ,CHARINDEX('first', Sample2TextColumn2) as SearchPos
     ,(LEN(Sample2TextColumn2) - LEN(REPLACE(Sample2TextColumn2,LTRIM(RTRIM('first')), '')))/LEN(LTRIM(RTRIM('first'))) as Counts
     ,1 AS Rank 
     ,'Section 1' AS Section 
  FROM
       SampleTable2 a
        WHERE CONTAINS((Sample2Text, Sample2TextColumn2) , @searchString)
     )
    ,CTE2 as 
    (SELECT  
      b.Id 
     ,dbo.StripHTML(TextColumn) as Target 
     ,CASE When LEN(TextColumn) <= 60
             Then TextColumn 
             ELSE LEFT(TextColumn, 60) + '...'
          END As BeginningOfString
     ,CHARINDEX('first', TextColumn) as SearchPos
     ,(LEN(TextColumn) - LEN(REPLACE(TextColumn, LTRIM(RTRIM('first')), '')))/LEN(LTRIM(RTRIM('first'))) as Counts
     ,2 AS Rank 
     ,'Section 2' AS Section
  FROM
       SampleTable b
        WHERE CONTAINS(TextColumn , @searchString)
    )
    INSERT INTO @searchData
        select *, case when SearchPos > 60 then substring(Target, SearchPos - 60, 60) + @searchString + 
substring(Target, SearchPos + len(@searchString), 60) else substring(Target, 1, SearchPos-1) + @searchString + substring(Target, SearchPos + len(@searchString), 60)
 end as NewString
from cte 

union all
        select *, case when SearchPos > 60 then substring(Target, SearchPos - 60, 60) + @searchString + 
substring(Target, SearchPos + len(@searchString), 60) else substring(Target, 1, SearchPos-1) + @searchString + substring(Target, SearchPos + len(@searchString), 60)
 end as NewString
from cte2 

Select  ROW_NUMBER() OVER(ORDER BY GetDate() DESC) AS RowId
       , x.* 
       From @searchData x
       order by x.Counts desc
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
          ,ERROR_SEVERITY() AS ErrorSeverity
          ,ERROR_STATE() AS ErrorState
          ,ERROR_PROCEDURE() AS ErrorProcedure
          ,ERROR_LINE() AS ErrorLine
          ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

Open in new window



using this to strip any html

ALTER function [dbo].[StripHTML]( @text varchar(max) ) returns varchar(max) as
begin
    declare @textXML xml
    declare @result varchar(max)
    set @textXML = REPLACE( @text, '&', '' );
    with doc(contents) as
    (
        select chunks.chunk.query('.') from @textXML.nodes('/') as chunks(chunk)
    )
    select @result = contents.value('.', 'varchar(max)') from doc
    return @result
end

Open in new window



And here is some sample data

CREATE TABLE [dbo].[SampleTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TextColumn] [nvarchar](1000) NOT NULL,
 CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[SampleTable2]    Script Date: 4/13/2014 7:59:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SampleTable2](
    [Sample2Id] [int] IDENTITY(1,1) NOT NULL,
    [Sample2Text] [nvarchar](max) NOT NULL,
    [Sample2TextColumn2] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_SampleTable2] PRIMARY KEY CLUSTERED 
(
    [Sample2Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[SampleTable] ON 

INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (1, N'This is the first Test string')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (2, N'This is the second one')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (3, N'This is the first really long string of text that should be included in the result set.')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (4, N'This is the second long string that will not be returned in the result set')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (5, N'This is a really really long result set that should also be first included in the result set.  It has a seperate sentence in it as well.')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (6, N'Now this is a really really first one.   It is so long that I have forgotten how long it really was.   Well it could be really long but first lets do this.  ')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (7, N'<p>Hello, </p>

<p>This is a really long first string with html inside</p>

<p>This is another line </p>

<p>This is a first line</p>

<p>This one is another line that is really really long, first</p>

<p>First, !=First</p>

<p>I want to see if it will find all the first lines.</p>

<p>THIS IS A CAPITAL LETTER LINE, THE LINE WILL SKIP A LINE</p>

<p>HERE IS THE FIRST LINE </p>')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (8, N'<p>This markup is copies from Microsoft Word Firstly</p>

<p>This Has some tabs.</p>

<p>First I want to know it it can count them.</p>

<p><b>The counts should return 4 firsts first</b></p>

<p>Well Maybe five first.</p>')
SET IDENTITY_INSERT [dbo].[SampleTable] OFF
SET IDENTITY_INSERT [dbo].[SampleTable2] ON 

INSERT [dbo].[SampleTable2] ([Sample2Id], [Sample2Text], [Sample2TextColumn2]) VALUES (1, N'This is the first text string in this table.

First it will find it all.  Maybe it will find a lot more, but I dunno, I am just typing firstly.

This should be found in the wildcard FIRst.  Maybe.  I am not sure.

This is one of the targets (First).

Maybe it will find ‘first’

First!!!!
', N'<p>This is the first text string in this table.</p>

<p>First it will find it all. Maybe it will find a lot more, but I dunno, I am just typing firstly.</p>

<p>This should be found in the wildcard FIRst. Maybe. I am not sure.</p>

<p>This is one of the targets (First).</p>

<p>Maybe it will find ‘first’</p>

<p>First!!!!</p>

<p><br />
</p>

<p><br />
</p>

<p>I just copied this text inside and converted it to html for the column</p>

<p><br />
</p>

<p>It might work, but im still not sure First -- first</p>
')
SET IDENTITY_INSERT [dbo].[SampleTable2] OFF

Open in new window

0
Comment
Question by:HockeyDude
1 Comment
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 39999698
The problem is that you'll get 0 as SearchPos for your second row..

DECLARE @searchString NVARCHAR(255) = '%'; 

WITH CTE2 AS (
		SELECT	b.Id ,
			dbo.StripHTML(TextColumn) AS [Target] ,
			CASE WHEN LEN(TextColumn) <= 60 THEN TextColumn
				ELSE LEFT(TextColumn, 60) + '...'
			END AS BeginningOfString ,
			CHARINDEX('first', TextColumn) AS SearchPos ,
			(LEN(TextColumn) - LEN(REPLACE(TextColumn, LTRIM(RTRIM('first')), ''))) / LEN(LTRIM(RTRIM('first'))) AS Counts ,
			2 AS Rank ,
			'Section 2' AS Section
		FROM	SampleTable b
		--WHERE CONTAINS(TextColumn , @searchString) 
		WHERE TextColumn LIKE @searchString
		)
	SELECT	*,
		CASE WHEN SearchPos > 60 THEN SUBSTRING([Target], SearchPos - 60, 60) + @searchString + SUBSTRING([Target], SearchPos + LEN(@searchString), 60)
			WHEN SearchPos > 0  THEN SUBSTRING([Target], 1, SearchPos-1) + @searchString + SUBSTRING([Target], SearchPos + LEN(@searchString), 60)
			ELSE [Target]
		END AS NewString
	FROM	cte2;

Open in new window


Caveat: You function [dbo].[StripHTML] does not work correctly. E.g.

SELECT	dbo.StripHTML('<p>This is valid HTML containing an ampersand: &amp;. D''oh, where''s my ampersand character?</p>');
SELECT	dbo.StripHTML('<p>This is valid HTML containing a line break.<br>Oops, no break.</p>');

Open in new window

0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question