Solved

Procedure Breaks when using parameter string

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

776 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