Extractng a value from string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Extractng a value from string

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2011-06-30 : 06:04:21

Dear All,

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

Begin Calculationis \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 \n \nEnd Quote Calculation \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.

kashyap.2000
Starting Member

22 Posts

Posted - 2011-06-30 : 06:21:47

Try this query ( but this is not flexible if data changes

)

select substring(calculation,charindex(':',calculation)+1,4) from pr_cal table

I love My India.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-30 : 07:22:03

[code]
Declare @str varchar(max)

Set @str =
'Begin Calculationis \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 \n \nEnd Quote Calculation \n'

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

Select
SUBSTRING(@str,start,finish-start)
From
(
Select
start = charindex(@key,@str,1)+LEN(@key)+1,
finish = charindex(' ',@str,charindex(@key,@str,1)+LEN(@key)+1)
) A
[/code]

Corey

I Has Returned!!

satheesh
Posting Yak Master

152 Posts

Posted - 2011-06-30 : 07:57:32

Thanks for your reply Seventhnight

Its working like charm.But this is for given string.Could your please tell me how to extarct the value for all the rows in the column calculation ie)pr_cal.Calculation

I tried to amend your query, but got error.Your help is highly appreciated.

Regards,
SG

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-30 : 08:50:22

What did you try?

Should be very similar:


Select
	SUBSTRING(YourColumn,start,finish-start)
From
	(
	Select 
		YourColumn,
		start = charindex(@key,YourColumn,1)+LEN(@key)+1,
		finish = charindex(' ',YourColumn,charindex(@key,YourColumn,1)+LEN(@key)+1)
	From YourTable
	) A

Corey

I Has Returned!!

satheesh
Posting Yak Master

152 Posts

Posted - 2011-06-30 : 09:59:32

Thanks Seventhnight

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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-30 : 11:13:35

maybe this will do it?

It would be helpful if you knew what data it was blowing up on...


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 = isnull(nullif(charindex(' ',calculation,charindex(@key,calculation,1)+LEN(@key)+1),0),len(calculation))
From pr_cal 
) A

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources