Error on TSQL

Question

Error on TSQL

luissantos

SSCertifiable

Points: 7391
More actions
March 8, 2010 at 3:11 pm

#156759

Hello comunity
I have this TSQL to test before transform the same in UPDATE Statment, but i have the following error:
if i write like above, that´s Ok :
select conta, left(dcli.oldesc,charindex('-',dcli.oldesc)-1),dcli.oldesc,
ltrim(rtrim(conta))+' '+right(ltrim(rtrim(dcli.oldesc)),
len(ltrim(rtrim(dcli.oldesc)))-(charindex('-',dcli.oldesc)-1)),
* from dcli where conta = '68123003'
If i write like this :
select conta, left(dcli.oldesc,charindex('-',dcli.oldesc)-1),dcli.oldesc,
ltrim(rtrim(conta))+' '+right(ltrim(rtrim(dcli.oldesc)),
len(ltrim(rtrim(dcli.oldesc)))-(charindex('-',dcli.oldesc)-1)),
* from dcli where conta <> left(dcli.oldesc,charindex('-',dcli.oldesc)-1)
I receive the following error:
Msg 536, Level 16, State 5, Line 2
Invalid length parameter passed to the SUBSTRING function.
Someone could give me help.
Many thanks
Luis Santos

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Could you provide sample data?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WayneS SSC Guru Points: 95460 More actions · Answer 2

luissantos (3/8/2010)
If i write like this :
select conta, left(dcli.oldesc,charindex('-',dcli.oldesc)-1),dcli.oldesc,
ltrim(rtrim(conta))+' '+right(ltrim(rtrim(dcli.oldesc)),
len(ltrim(rtrim(dcli.oldesc)))-(charindex('-',dcli.oldesc)-1)),
* from dcli where conta <> left(dcli.oldesc,charindex('-',dcli.oldesc)-1)
I receive the following error:
Msg 536, Level 16, State 5, Line 2
Invalid length parameter passed to the SUBSTRING function.
Someone could give me help.
Many thanks
Luis Santos

If the field doesn't have "-", then you are passing a -1 to the LEFT function, which will fail with the above error.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

WayneS (3/8/2010)
luissantos (3/8/2010)
If i write like this :
select conta, left(dcli.oldesc,charindex('-',dcli.oldesc)-1),dcli.oldesc,
ltrim(rtrim(conta))+' '+right(ltrim(rtrim(dcli.oldesc)),
len(ltrim(rtrim(dcli.oldesc)))-(charindex('-',dcli.oldesc)-1)),
* from dcli where conta <> left(dcli.oldesc,charindex('-',dcli.oldesc)-1)
I receive the following error:
Msg 536, Level 16, State 5, Line 2
Invalid length parameter passed to the SUBSTRING function.
Someone could give me help.
Many thanks
Luis Santos
If the field doesn't have "-", then you are passing a -1 to the LEFT function, which will fail with the above error.

You just had to go and spoil it...:-D:-D:-D

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events