Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Invalid length parameter passed to the LEFT or SUBSTRING function.

Respondido Invalid length parameter passed to the LEFT or SUBSTRING function.

  • Wednesday, February 13, 2013 10:21 PM
     
      Has Code
    Sign In to Vote
    0

    Hi,
    I'm using the Substring and Charindex to break values which is in the format:

    field1
    InfoPath, Publisher",551,407,72,0
    21,20,210,0
    <blank>
    Publisher",0,47,2,0

    Here I'm breaking the value on comma limited

    field2      field3       field4       field5
    551         407          72            0
    21           20            210          0

    0              47           2              0

    I was able to break the characters before commas with the following syntax:

    SUBSTRING([field1], CHARINDEX('"',[field1])+1, LEN([field1])-(CHARINDEX('"',[field1])))As field2

    Any addition to the above syntax, resulting me with an error: Invalid length parameter passed to the LEFT or SUBSTRING function.

    Can anyone let me know where the mistake is



    • Edited by K.Kalyan Wednesday, February 13, 2013 10:23 PM edit
    • Edited by K.Kalyan Wednesday, February 13, 2013 10:42 PM edit
    •  
     

All Replies

  • Wednesday, February 13, 2013 10:45 PM
     
     Answered Has Code
    Sign In to Vote
    0
    try this, this might help 
    create table sname1 (ID varchar(20))
insert into sname1
values ('234,565,6565,00')
,('24,56,65,60')

select  dbo.SplitIndex (',',ID,1),
dbo.SplitIndex (',',ID,2),
dbo.SplitIndex (',',ID,3),
dbo.SplitIndex (',',ID,4)
from sname1

    you can get the code for splitindex function here http://stackoverflow.com/questions/11185318/how-to-separate-string-into-different-columns

    Hope it Helps!!

    • Marked As Answer by K.Kalyan Wednesday, February 13, 2013 11:17 PM
    •  
     
  • Wednesday, February 13, 2013 11:02 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0
     
    create table test (field1 varchar(200))
insert into test values ('InfoPath, Publisher",551,407,72,0'),('21,20,210,0'),(''),('Publisher",0,47,2,0')

select  
parsename(fields,4) as field2,
parsename(fields,3) as field3, 
parsename(fields,2) as field4, 
parsename(fields,1) as field5 
FROM 
(select replace(substring(field1,Case WHEN  charindex( '",',field1)> 0 Then   charindex( '",',field1)+2 Else 1 END, len(field1) ),',','.') as fields

FROM test) t



drop table test

    • Marked As Answer by K.Kalyan Wednesday, February 13, 2013 11:17 PM
    •  
     
  • Wednesday, February 13, 2013 11:19 PM
     
      Has Code
    Sign In to Vote
    0

    Probably the error is when dealing with 3rd row (blank). Here's another approach -

    DECLARE @t TABLE(field VARCHAR(50))
INSERT INTO @t
VALUES('InfoPath, Publisher",551,407,72,0'),('21,20,210,0'),('<blank>'),('Publisher",0,47,2,0')
SELECT * FROM @t
SELECT f0.field1,f1.field2,f2.field3,f3.field4
FROM @t
CROSS APPLY (SELECT PATINDEX('%[0-9]%',field) AS p1) s1
CROSS APPLY (SELECT CHARINDEX(',',field,s1.p1) AS p2)s2
CROSS APPLY (SELECT CASE WHEN s1.p1 <>0 THEN SUBSTRING(field,s1.p1,s2.p2-s1.p1) END AS field1) f0
CROSS APPLY (SELECT CHARINDEX(',',field,s2.p2+1) AS p3)s3
CROSS APPLY (SELECT CASE WHEN s2.p2<>0 THEN SUBSTRING(field,s2.p2+1,s3.p3-s2.p2-1) END AS field2)f1
CROSS APPLY (SELECT CHARINDEX(',',field,s3.p3+1) AS p4)s4
CROSS APPLY (SELECT CASE WHEN s3.p3<>0 THEN SUBSTRING(field,s3.p3+1,s4.p4-s3.p3-1) END AS field3)f2
CROSS APPLY (SELECT CASE WHEN s4.p4 <>0 THEN SUBSTRING(field,s4.p4+1,LEN(field)) END AS field4)f3


    Narsimha