Invalid length parameter passed to the substring function.

• Sree-258588

  Mr or Mrs. 500

  Points: 533

  More actions

  February 3, 2010 at 11:48 pm

  #141083

  Hi

  declare @filename varchar(100)

  set @filename = 'Sree Mill-(1-22-1956)-19223.doc'

  select * from ftpfile

  where LTRIM(RTRIM(SUBSTRING(fileName, 1, CHARINDEX('.', fileName) - 1)))

  = LTRIM(RTRIM(SUBSTRING(@FILENAME, 1, CHARINDEX('.', @FILENAME) - 1)))

  I'm getting the above error 536

  ---

  MY table has filenames like

  Sree Mill (1-22-1956)-19223.doc

  Vaye (1-22-1956)

  Kris Mill (1-22-1956)-19223.doc

  ----

  If i'm searching with @filename = 'Kris Mill (1-22-1956)-19223.doc' which exists in the table then -> I'm getting the details of that record.

  But if i give the filename which doesn't exist then getting the error.

  -----

  I've searched lot of articles on this:

  changed the query to:

  select * from ftpfile

  where LTRIM(RTRIM(SUBSTRING(fileName, 1, Nullif(CHARINDEX('.', fileName) - 1,-1))))

  = LTRIM(RTRIM(SUBSTRING(@FILENAME, 1, nullif(CHARINDEX('.', @FILENAME) - 1,-1))))

  now if i give correct or incorrect filename i didn't get the error

  -------------

  My question is how come then in the previous query I got the results with correct file name. How does the select statement query the rows??

  Thinking in depth..

  Thanks in advance ..

  Sree

• arun.sas

  SSChampion

  Points: 11831

  More actions

  February 4, 2010 at 12:10 am

  #1113498

  Hi

  One of the record in the ftpfile table does’t have the DOT(“.”) character and same which you search in the charindex/substring, hence this error occurred.

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 4, 2010 at 12:12 am

  #1113499

  arun.sas (2/4/2010)

  ---

  Hi

  One of the record in the ftpfile table does’t have the DOT(“.”) character and same which you search in the charindex/substring, hence this error occurred.

  As Arun said, the character is not found in the string. When that happens you will get the error message you described.

  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

• Sree-258588

  Mr or Mrs. 500

  Points: 533

  More actions

  February 5, 2010 at 7:12 am

  #1114408

  Ya..as I said..we get the error and I resolved it..But when I compare with correct file name I didn't get the error. How come?

  when I give a different file name then only the error occurs ---

  This is really keeping me in dilemma..

  hope I'm clear...

  Thanks in advance

  Sree