1. Registered User
Join Date
Jun 2011
Posts
6

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(@k ey)+1)
From pr_cal
) A

Result:
It returns few rows for eg:
-3.6847058823529411764705882353<br
-7.4565766786786564342346795345<br
-3.6842684690456865422468005352<br

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

2. Registered User
Join Date
Mar 2007
Location
Holmestrand, Norway
Posts
332
Most of the times I get this error, it is due to rows that does not contain the search pattern. Keep in mind that whenever search pattern is not found, it returns 0. For your code to work as intended, finish have to be greater than start.

3. Registered User
Join Date
Nov 2004
Posts
1,427
Try
Code:
```DROP TABLE #MyTable
CREATE TABLE #MyTable(
calculation	VARCHAR(500)	NOT NULL
)

INSERT INTO #MyTable VALUES
('This Level: 12345 hjhj hjh hj hj This Level: '),
(' gh ghj ghf gdhfj This Level: '),
('hj hjh jhjh j')

Declare @key varchar(100)
Set @key = 'Level: '

Select	SUBSTRING(
calculation,
charindex(@key, calculation, 1) + LEN(@key) + 1,
charindex(' ', calculation, charindex(@key, calculation, 1) + LEN(@key) + 1) -
(charindex(@key, calculation, 1) + LEN(@key) + 1)
)
From #MyTable
WHERE charindex(@key, calculation, 1) > 0 AND
charindex(' ', calculation, charindex(@key, calculation, 1) + LEN(@key) + 1) > 0```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•