Extracting a Value from string

Question

question

DataAnalyst asked Jun 30, '11

Extracting a Value from string

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

sql-server-2008 string

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Jun 30, 2011 at 04:11 AM

I've tried to format the data in the question, but am not sure if the markup is part of the data or added by the OP in an attempt to format.

0 ·

DataAnalyst commented · Jun 30, 2011 at 04:42 AM

Thanks For your reply The real string will some thing similar to Begin Calculation
\n
\n*Passed*
\nId: 492
\nDuration: 8d
\nRType: Ind
\nECodes: L1
\nPId: 1339
\nType: ST
\nTStype:
\ncst Ages: 19
\narea: A1
\n
\n------
\n*Records/Resolved Types*
\nFound APrd with Id: 13
\nFBPId: 501
\nRSId: 172
\nTax Codeid: 1
\nCurrency: $
\n TType To: 1 with Ttype: 0
\nRs: Ind
\n
\n------
\n* Rates*
\nBase: 3.480000
\nWeeks BRate: 0
\n
\n Cst: 1
\n PPrice: 3.48000000
\n Extra price: 0.00
\n RL1 percentage: 90.00
\n EL1 Price: -0.3480000000
\nE Price: -0.3480000000
\n cstTotal Price: 3.1320000000
\n
\n*Prices (After Resolving cst*
\nBPrice: 3.48000000
\nExtra B Price: 0.00
\nBPrice: -0.34800
\n

\n
\n------
\n*Commissions for Level: 0*
\n PPCommission Percentage: 15.00
\n Percentage: 15.00
\n
\n cst 1
\nPrice including Commission: 4.0941176
cstTotal Price including Commission: 3.68470588253
\n
\n Total Commission at This Level: 0.5527058823529411764705882353
\n Total Price including Commission at This Level: 3.6847058823529411764705882353
\n
\n
Cst: 1
\n PPrice: 5.89793
\n Extra price: 1.00
\n RL1 percentage: 100.00
\n EL1 Price: -8.656400000
\nE Price: -2.65000000
\n cstTotal Price: 7.6570000
\n
\n*Prices (After Resolving cst*
\nBPrice: 8.5435000
\nExtra B Price: 1.00
\nBPrice: -5.340000000
\n

\n
\n------
\n*Commissions for Level: 1*
\n PPCommission Percentage: 18.00
\n Percentage: 19.00
\n
\n cst 1
\nPrice including Commission: 9.5671176
cstTotal Price including Commission: 112705882353
\n
\n Total Commission at This Level: 1.8823529482353
\n Total Price including Commission at This Level: 7.43555411353
\n
\n
/>\n
\nEnd Calculation
\n

0 ·

Fatherjack ♦♦ commented · Jun 30, 2011 at 05:38 AM

@kev riley - the 2nd BR tag has no closing > . I added one when I reformatted ...

0 ·

Answer 1 · 2011-06-30T04:09:38Z

Tim answered Jun 30, '11

It really helps if we can have an example of the full string. However you will most likely need to use [substring]( http://msdn.microsoft.com/en-us/library/ms187748.aspx) in connection with [charindex]( http://msdn.microsoft.com/en-us/library/ms186323.aspx) and or [patindex]( http://msdn.microsoft.com/en-us/library/ms188395.aspx). If you can provide a sample of the actual string, feel free to replace any values in the string that contain customer data.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2011-06-30T04:11:50Z

Pavel Pawlowski answered Jun 30, '11

The easies way is to use a CLR RegEx implementation. You can take a look on sample CLR RegEx implementation in my article [SQL Server 2005 and SQL Server 2008 Regular Expressions and pattern matching][1]. Also there could be a possible way of using pure T-SQL by PATINDEX etc. If you would like to go by pure T-SQL, search the site for the PATINDEX and you will find many examples. [1]: http://www.pawlowski.cz/2010/09/sql-server-2005-and-sql-server-2008-regular-expressions-and-pattern-matching-2/

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski commented · Jun 30, 2011 at 11:10 AM

Here is example using the sample CLR RegEx implementation using your data. You get the commission by simple RegEx expression: **`'Total Price including Commission at This Level:\s*?(\d+.\d+|\d+)'`** And a whole example: (I've shortened your string a little bit so it isn't so long. but generally it doesn't matter how long the string is) DECLARE @str nvarchar(max) SET @str = ' Begin Calculation nFound APrd with Id: 13 nPrices (After Resolving cst nBPrice: 3.48000000 nPrice including Commission: 4.0941176 cstTotal Price including Commission: 3.68470588253 n n Total Commission at This Level: 0.5527058823529411764705882353 n Total Price including Commission at This Level: 3.6847058823529411764705882353 Cst: 1 nE Price: -2.65000000 n cstTotal Price: 7.6570000 nBPrice: 8.5435000 nExtra B Price: 1.00 n Percentage: 19.00 cstTotal Price including Commission: 112705882353 n n Total Commission at This Level: 1.8823529482353 n Total Price including Commission at This Level: 7.43555411353 n n' SELECT ClrSafe.fn_RegExMatch(@str, 'Total Price including Commission at This Level:\s*?(\d+.\d+|\d+)', 1, 1) Result is: **`3.6847058823529411764705882353`**

0 ·

question