Left Function and CHARINDEX

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  March 30, 2015 at 11:32 am

  #299094

  SELECT LEFT(InstanceName,CHARINDEX(' \ ',InstanceName)-1)

  FROM ServerDB

  Returns the following error.

  Invalid length parameter passed to the SUBSTRING function.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  March 30, 2015 at 11:54 am

  #1787452

  That happens when the backslash doesn't exist in the string and generates 0-1=-1 which is invalid for the parameter.

  Use a safety net to prevent this and remove the spaces (unless you need them).

  SELECT LEFT(InstanceName,CHARINDEX('\',InstanceName+'\')-1)

  FROM ServerDB

  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

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  March 30, 2015 at 11:56 am

  #1787453

  Luis Cazares (3/30/2015)

  ---

  That happens when the backslash doesn't exist in the string and generates 0-1=-1 which is invalid for the parameter.

  Use a safety net to prevent this and remove the spaces (unless you need them).

  SELECT LEFT(InstanceName,CHARINDEX('\',InstanceName+'\')-1)

  FROM ServerDB

  Sweet. Thanks!

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/