Charindex

[gsavitha]

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<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

 

[markros]

Declare @key varchar(100)
Set @key = 'Total Price including Commission at This Level: '

Select case when Start>0 and Finish > 0 and Finish > Start then
SUBSTRING(calculation,start,finish-start) else Calculation end
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

We need to make sure each record conforms the above pattern - if not, we need to add a check for Start, Finish.

PluralSight Learning Library

 

[gsavitha]

Thanks for your reply markros

I try to execute the script, it returns the whole string as it is.Its not extracting the integer value.Please let me know how to amend the query further.

Reagrds,
SG

 

[markros]

Can you post some sample data? It should work OK as long as your Calculation field contains the string you showed.

Also, I suggest to include both values (Finish, Start) in the list of fields, then you'll see the problem.

PluralSight Learning Library

 

[gsavitha]

Hi Markors,

Logic:-

I have a 'calculation' coulmn(datatype:test) in pr_cal table.In this coulmn a big string is stored. for eg:

Begin Calculationis <br />\n<br simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard galley Total Price including Commission at This Level: 7.6847058823529411764705882353 the leap into is simply been the industry's standard dummy text Gross Price with Surcharges : 6.14
It Lorem Ipsum survived not only nProduct Price including Commission: 5.68627 five centuries, but also the leap into since the 1500s, when an unknown Total Price including Commission at This Level: 17.14567335 scrambled it to make a type specimen book.Total Price including Commission at This Level: 27.6847 <br />\n<br />\nEnd Quote Calculation<br />\n"



I need to extract the value next to 'Total Price Including Commision at this level: " here it be 7.684 and need to store in seperate column.

Note:This "Total Price Including Commision at this level" string may occurs many time in the text. I need to get the value next to first one.

Regards,
SG

 

[markros]

I usually do parsing with CROSS APPLY technique (SQL 2005+).

Take a look at this blog post for details
Parsing the Address field

PluralSight Learning Library