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

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  March 8, 2010 at 4:59 pm

  #1130351

  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

  March 8, 2010 at 6:17 pm

  #1130367

  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

  March 9, 2010 at 8:48 am

  #1130717

  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