Solved

Procedure Breaks when using parameter string

Posted on 2014-04-14
1
122 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 32

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now