[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Procedure Breaks when using parameter string

Posted on 2014-04-14
1
Medium Priority
?
145 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 35

Accepted Solution

by:
ste5an earned 1500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

649 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