CODEWITHSUNDEEP
sqlsql-servert-sql
Coder Guy
Coder Guy

Reputation: 7

Why error Invalid length parameter passed to the LEFT or SUBSTRING function?

I am using this to trim the data to the left of pipe sign, means I only want data to the left of pipe sign else whole if no pipe sign is there but it throws error.

Error:

Invalid length parameter passed to the LEFT or SUBSTRING function.

Code:

      SELECT TOP 1000 [ID]
          ,case
        when CHARINDEX('|', ProjectNo) > 0 then
            rtrim(left(ProjectNo, CHARINDEX('(', ProjectNo) - 1))
        else
            ProjectNo
      end ProjectNo

Upvotes: 0

Views: 440

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can just append the character you are looking for in the CHARINDEX() expression:

SELECT TOP 1000 [ID],
       RTRIM(LEFT(ProjectNo, CHARINDEX('|', ProjectNo + '|') - 1)) as  ProjectNo

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37337

This is because CHARINDEX('(', ProjectNo) - 1 might give negative value which is invalid, as error states. I belive you want:

SELECT TOP 1000 [ID]
,case when CHARINDEX('|', ProjectNo) > 0 then
        rtrim(left(ProjectNo, CHARINDEX('|', ProjectNo) - 1))
      else ProjectNo
 end ProjectNo

Upvotes: 3

Ed Bangga
Ed Bangga

Reputation: 13006

this should be

 SELECT TOP 1000 [ID]
          ,case
        when CHARINDEX('|', ProjectNo) > 0 then
             rtrim(left(ProjectNo, CHARINDEX('|', ProjectNo) - 1))
        else
            ProjectNo
      end ProjectNo

you are getting '|' not '('

or

 SELECT TOP 1000 [ID]
              ,case
            when CHARINDEX('(', ProjectNo) > 0 then
                 rtrim(left(ProjectNo, CHARINDEX('(', ProjectNo) - 1))
            else
                ProjectNo
          end ProjectNo

Upvotes: 0

Related Questions