asked
Charindex and Substring
Dear all, Declare @key varchar(100) Set @key = 'Total Price including Commission at This Level: ' Select SUBSTRING(calculation,start,finish-start) From ( Select calculation, start = charindex(@key,calculation,1)+LEN(@key)+1, finish = charindex(' ',calculation,charindex(@key,calculation,1)+LEN(@key)+1) From pr_cal ) A Result: It returns few rows for eg: -3.6847058823529411764705882353 -7.4565766786786564342346795345 -3.6842684690456865422468005352 and then i got "Invalid length parameter passed to the LEFT or SUBSTRING function" error Please any one can let me know, How to amend this Query to avoid the above error and i need only value for ex -3.684.Thanks. Regards, SG
SUBSTRING need to have usable values > start_expression > Is an integer or > bigint expression that specifies where > the returned characters start. If > start_expression is less than 1, the > returned expression will begin at the > first character that is specified in > value_expression. In this case, the > number of characters that are returned > is the largest value of either the sum > of start_expression and > length_expression or 0. If > start_expression is greater than the > number of characters in the value > expression, a zero-length expression > is returned. > > length_expression > Is a positive > integer or bigint expression that > specifies how many characters of the > value_expression will be returned. If > length_expression is negative, an > error is generated and the statement > is terminated. If the sum of > start_expression and length_expression > is greater than the number of > characters in value_expression, the > whole value expression beginning at > start_expression is returned. If your values "start" and "finish-start" dont comply with these rules then you will get that error
