Invalid length paramter passed to substring function

• mcfarlandparkway

  SSCertifiable

  Points: 7699

  More actions

  May 2, 2017 at 9:14 am

  #325355

  hen I ran this query I am facing this error. Any advice please

  select CASE

  WHEN CHARINDEX('-','LOHAUANIA') <> 0

  THEN substring(CAST('LOHAUANIA' AS VARCHAR(20)),1,CAST(CHARINDEX('-','LOHAUANIA') AS VARCHAR(20)) -1)

  ELSE 'AB'

  END

• Thom A

  SSC Guru

  Points: 99130

  More actions

  May 2, 2017 at 9:33 am

  #1940730

  Although this doesn't work for string literals, it works fine when using a dataset. For example:
  CREATE TABLE #test (string varchar(20));
GO

  INSERT INTO #test
VALUES ('LOHAUANIA');
GO

  SELECT CASE CHARINDEX('-',string)
       WHEN 0 THEN 'AB'
       ELSE substring(CAST(string AS VARCHAR(20)),1,CAST(CHARINDEX('-',string) AS VARCHAR(20)) -1)
       END
FROM #test;

  --Or your CASE version:
SELECT CASE WHEN CHARINDEX('-','string') <> 0 THEN substring(CAST(string AS VARCHAR(20)),1,CAST(CHARINDEX('-',string) AS VARCHAR(20)) -1)
       ELSE 'AB'
       END
FROM #test
GO
DROP TABLE #test;

  I'm sure someone better versed in the inner mind of SQL will be able to advise my string literals don't work when a dataset does. My guess would be that SQL evaluates all the the options when processing only literals?

  May I ask why you are checking for a character in a literal string that you know isn't contained in it?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  May 2, 2017 at 9:34 am

  #1940732

  SUBSTRING takes a string argument followed by two integer arguments.  I suspect you wanted the final argument to look more like CHARINDEX('-','LOHAUANIA') - 1.  Even then, you will still get an error, given that the final argument evaluates to -1, since "-" isn't found in "LOHAUNIA".

  John

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  May 2, 2017 at 9:43 am

  #1940733

  Possible workaround using NULLIF

  select CASE

     WHEN CHARINDEX('-','LOHAUANIA') <> 0

       THEN substring(CAST('LOHAUANIA' AS VARCHAR(20)),1,CAST(NULLIF(CHARINDEX('-','LOHAUANIA'),0) AS VARCHAR(20)) -1)

           ELSE 'AB'

           END

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 2, 2017 at 11:19 am

  #1940744

  This only happens when using only literals. If you're using literals, there's no need to have complex logic. So, here are some examples on how to avoid errors or how to make things simpler if the rules allow it.
  
SELECT CASE
    WHEN CHARINDEX('-',String) <> 0
      THEN substring(CAST(String AS VARCHAR(20)),1,CAST(CHARINDEX('-',String) AS VARCHAR(20)) -1)
    ELSE 'AB'
   END AS OriginalWithColumns,
   CAST(LEFT(String, CHARINDEX('-',String + '-') - 1) AS VARCHAR(20)) AS SimplifiedWithColumns
FROM (VALUES('LOHAUANIA'), ('LOHAUANIA-'), (''), ('LOHAU-ANIA'))SampleData(String)
GO
SELECT CASE
    WHEN CHARINDEX('-','LOHAUANIA') <> 0
      THEN substring(CAST('LOHAUANIA' AS VARCHAR(20)),1,CAST(NULLIF(CHARINDEX('-','LOHAUANIA' + '-'),0) AS VARCHAR(20)) -1)
    ELSE 'AB'
   END AS OriginalWithSafeNet
GO
SELECT CASE
    WHEN CHARINDEX('-','LOHAUANIA') = 0
      THEN 'AB'
    ELSE substring(CAST('LOHAUANIA' AS VARCHAR(20)),1,CAST(NULLIF(CHARINDEX('-','LOHAUANIA'),0) AS VARCHAR(20)) -1)
   END AS OriginalWithInvertedConditions
GO


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2