- Home /

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

Comment

Scot Hauder

**Answer** by Fatherjack
·
Jun 30, 2011 at 07:29 AM

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

Copyright 2018 Redgate Software. Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Ask SSC Site Issues (meta-askssc)
- Explore
- Topics
- Questions
- Users
- Badges