Extracting Value from String

[gsavitha]

Dear All,

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.

Sorry for this example,as it need to explian bit clearly about my requirtment.Any help will be highly appreciated.

Regards,
SG

 

[gmmastros]

Please take a look at this blog I wrote:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server

If that does not work for you, please post back here.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

declare @test varchar(8000)
set @test ='Begin Calculationis <br />\n<br simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard galley Total Price including Commission at This Level: 7.6847058823529411764705882353 the leap into is simply been the industrys standard dummy text Gross Price with Surcharges : 6.14It 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'

declare @search varchar(100)
--Len of @search plus a space is 48
set @search ='Total Price Including Commission at this level:'

select charindex(lower(@search), lower(@test))+48 as start
,charindex(' ', @test, charindex(lower(@search), lower(@test))+48) as [end]
,substring(@test, charindex(lower(@search), lower(@test))+48,
(charindex(' ', @test, charindex(lower(@search), lower(@test))+48)) -
(charindex(lower(@search), lower(@test))+48)) as [Value]


replace @test with your field name and add a from statement.

Simi