none
Invalid length parameter passed to the LEFT or SUBSTRING function RRS feed

  • Question

  • I got "Invalid length parameter passed to the LEFT or SUBSTRING function"error from the following condition.

    --DROP TABLE #test

    CREATE TABLE #test
    (
    id INT,
    Name VARCHAR (50)
    )

    INSERT INTO #test (id, Name)
    VALUES 
     (1,  'Dee,Fang' ), ( 2, 'See,Fang' ),( 3,'LeeFang' ), (4,'GeeFang'), (5, 'Fee,Fang');

    SELECT * FROM #test


    SELECT  Name,
      LTRIM(RTRIM(SUBSTRING(Name, CHARINDEX(',', Name) + 1, 20)))  As Firstname
      ,LTRIM(RTRIM(SUBSTRING(Name, 1, CHARINDEX(',', Name) - 1))) As LastName
    FROM    #test

    How can I find the LIST OF NAME which does NOT have Comma (,) IN BETWEEN NAME column?

    Thank you
    Tuesday, July 11, 2017 2:51 AM

Answers

  • Hi Dee2018,

    From your post, it seems that you want to get the first name and the last name from the column "Name" in the table "test". However, you don't know how to get the values from the column "Name" whose values don't have comma. For this purpose, you may use PATINDEX function to meet your needs. Here is the example code for your reference:

    CREATE TABLE #test
     (
     id INT,
     Name VARCHAR (50)
     )
    
    INSERT INTO #test (id, Name)
     VALUES  
      (1,  'Dee,Fang' ), ( 2, 'See,Fang' ),( 3,'LeeFang' ), (4,'GeeFang'), (5, 'Fee,Fang');
    
    
    SELECT  Name,
            case when CHARINDEX(',', Name)!=0 then LTRIM(RTRIM(SUBSTRING(Name, CHARINDEX(',', Name) + 1, 20))) 
            else substring(Name,PATINDEX('%[A-Z]%' COLLATE Latin1_General_Bin,right(Name,len(Name)-1))+1,len(Name)-PATINDEX('%[A-Z]%' COLLATE Latin1_General_Bin,right(Name,len(Name)-1))) end as Firstname,
    		case when CHARINDEX(',', Name)!=0 then LTRIM(RTRIM(SUBSTRING(Name, 1, CHARINDEX(',', Name) - 1)))
    		else substring(Name,1,PATINDEX('%[A-Z]%' COLLATE Latin1_General_Bin,right(Name,len(Name)-1))) end as LastName
    		from #test

    Hope above could be helpful to you.

    Best Regards,

    Will



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 11, 2017 6:51 AM
    Moderator

All replies

  • Try

    SELECT  Name,
      LTRIM(RTRIM(SUBSTRING(Name, NULLIF(CHARINDEX(',', Name),0) + 1, 20)))  As Firstname
      ,LTRIM(RTRIM(SUBSTRING(Name, 1, NULLIF(CHARINDEX(',', Name),0) - 1))) As LastName
    FROM    #test
    
    select * from #test where Name not like '%,%' -- get list of names without comma

    Using NULLIF is a common technique to prevent the error with SUBSTRING.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by DeviantLogic Tuesday, July 11, 2017 4:14 AM
    Tuesday, July 11, 2017 3:17 AM
    Moderator
  • Awesome Naomi N Thank you!!!
    Tuesday, July 11, 2017 3:23 AM
  • And if you want to default to the last name

      LTRIM(RTRIM(SUBSTRING(Name, 1, ISNULL(NULLIF(CHARINDEX(',', Name),0) - 1, LEN(Name))))) As LastName

    Tuesday, July 11, 2017 3:26 AM

  • SELECT  Name,stuff(Name,1,CHARINDEX(',', Name) ,'') As Firstname,
    stuff(Name,CHARINDEX(',', Name) ,len(Name),'') As LastName
     
    FROM    #test
    Tuesday, July 11, 2017 4:17 AM
    Moderator
  • Hi Dee2018,

    From your post, it seems that you want to get the first name and the last name from the column "Name" in the table "test". However, you don't know how to get the values from the column "Name" whose values don't have comma. For this purpose, you may use PATINDEX function to meet your needs. Here is the example code for your reference:

    CREATE TABLE #test
     (
     id INT,
     Name VARCHAR (50)
     )
    
    INSERT INTO #test (id, Name)
     VALUES  
      (1,  'Dee,Fang' ), ( 2, 'See,Fang' ),( 3,'LeeFang' ), (4,'GeeFang'), (5, 'Fee,Fang');
    
    
    SELECT  Name,
            case when CHARINDEX(',', Name)!=0 then LTRIM(RTRIM(SUBSTRING(Name, CHARINDEX(',', Name) + 1, 20))) 
            else substring(Name,PATINDEX('%[A-Z]%' COLLATE Latin1_General_Bin,right(Name,len(Name)-1))+1,len(Name)-PATINDEX('%[A-Z]%' COLLATE Latin1_General_Bin,right(Name,len(Name)-1))) end as Firstname,
    		case when CHARINDEX(',', Name)!=0 then LTRIM(RTRIM(SUBSTRING(Name, 1, CHARINDEX(',', Name) - 1)))
    		else substring(Name,1,PATINDEX('%[A-Z]%' COLLATE Latin1_General_Bin,right(Name,len(Name)-1))) end as LastName
    		from #test

    Hope above could be helpful to you.

    Best Regards,

    Will



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 11, 2017 6:51 AM
    Moderator
  • Thanks Will_Kong!!!
    Tuesday, July 18, 2017 9:06 PM