Quick Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Quick Question

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-07-11 : 11:00:04

Hi Guys

I'm trying to run the following query:

UPDATE [DIUS.ECommerce01].[dbo].[TMP.WebDescriptions]
SET [Title] = left([FullText],charindex('|',[FullText])-1)
WHERE [Line Number] = 1

But get the following error:

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.

Does anyone know what I am doing wrong???

Thanks

raky
Aged Yak Warrior

767 Posts

Posted - 2008-07-11 : 11:32:43

Please post the full query.Where u have used the SUBSTRING Function which your error message contained?

mfemenel
Professor Frink

1421 Posts

Posted - 2008-07-11 : 15:08:51

I've typically seen that type of error when you're trying to manipulate a string that is null or shorter than expected. For example your left function above might be producing an invalid value for fulltext-1. Try a select statement that includes your left function and see if you can find the offending row. You may need to add a where clause that says where charindex('|',[FullText])>0 to only try this update on rows that contain | in fulltext.

Mike
"oh, that monkey is going to pay"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-12 : 00:54:10

quote:
Originally posted by rcr69er

Hi Guys

I'm trying to run the following query:

UPDATE [DIUS.ECommerce01].[dbo].[TMP.WebDescriptions]
SET [Title] = left([FullText],charindex('|',[FullText])-1)
WHERE [Line Number] = 1

But get the following error:

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.

Does anyone know what I am doing wrong???

Thanks

the error happens because you have values in fulltext field without | character which causes charindex to return 0 and so length parameter for left becomes -1 which is invalid. to avoid this, you need to modify it like this

UPDATE [DIUS.ECommerce01].[dbo].[TMP.WebDescriptions]
SET [Title] = left([FullText],CASE WHEN charindex('|',[FullText])>0 THEN  charindex('|',[FullText])-1 ELSE LEN([FullText]) END)
WHERE [Line Number] = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources