Forum Discussion

Aman0712's avatar
Aman0712
Copper Contributor
Oct 24, 2023

Is it a SSMS bug or am i doing it wrong?

declare @str varchar(20);

set @str = '$200'

SELECT CASE WHEN CHARINDEX(' ',@str,CHARINDEX('$',@str)+2) = 0 THEN 'aman' ELSE SUBSTRING(@str,CHARINDEX('$',@str),CHARINDEX(' ',@str,CHARINDEX('$',@str)+2)-CHARINDEX('$',@str)) end

------------------------------------------

SELECT CASE WHEN CHARINDEX(' ','$200',CHARINDEX('$','$200')+2) = 0 THEN 'aman' ELSE SUBSTRING('$200',CHARINDEX('$','$200'),CHARINDEX(' ','$200',CHARINDEX('$','$200')+2)-CHARINDEX('$','$200')) end

 

As both the queries are similar, the only difference is one is hardcoded value and one is passed with variable name str . Then why the second query giving invalid length parameter passed to the substring function while the first one does not give any such error.

1 Reply

  • Arshad440's avatar
    Arshad440
    Brass Contributor
    Oct 25, 2023

    Aman0712 This is not a SSMS bug as there is no syntax when using SUBSTRING() to give the length of  characters(third parameter of substring()) in Negative Integer(-).It will cause an error. 

Resources