Extract only part of text column

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

SQL Server 2005 Forums

Transact-SQL (2005)

Extract only part of text column

Author

Topic

tpavan7329
Starting Member

18 Posts

Posted - 2012-03-23 : 21:39:31

I have a table with column which comes in below text, i need to extract/select only actionGameShopPackPriceValue amount(which is 299 as per below) from this text, can i do this in sql server? Can someone help me with query?

{"module":"VShop","action":"proc.in.app.purchase.ok.req","formatVer":"1.0","gameId":9999,"userId":12345,"inTransactionId":"151111116505655","inTransactionStatus":0,"inTransactionReceipt":"xyx":"1","actionGameShopPackCount":1,"actionGameShopPackPriceCurrCode":"USD","actionGameShopPackPriceValue":"299","actionTransactionSumValue":299,"actionTransactionSumCurrCode":"USD","isSandbox":false,"outDeliveryVector":"1:200000"}

Thanks
Pavan

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-23 : 22:49:25

If your data always has the same pattern - it has the string actionGameShopPackPriceValue, followed by the double-quotes, colon etc. and if the value is in double-quotes, you can do the following. But, if your data does not follow those rules this will not work:

DECLARE @x VARCHAR(MAX);
SET @x = '{"module":"VShop","action":"proc.in.app.purchase.ok.req","formatVer":"1.0","gameId":9999,"userId":12345,"inTransactionId":"151111116505655","inTransactionStatus":0,"inTransactionReceipt":"xyx":"1","actionGameShopPackCount":1,"actionGameShopPackPriceCurrCode":"USD","actionGameShopPackPriceValue":"299","actionTransactionSumValue":299,"actionTransactionSumCurrCode":"USD","isSandbox":false,"outDeliveryVector":"1:200000"}';
SELECT LEFT(STUFF(@x,1,CHARINDEX('actionGameShopPackPriceValue":',@x)+30,''),
	CHARINDEX('"',STUFF(@x,1,CHARINDEX('actionGameShopPackPriceValue":',@x)+30,''))-1);

tpavan7329
Starting Member

18 Posts

Posted - 2012-03-24 : 01:24:32

Thank you it worked.

T Pavan Kumar

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-24 : 07:46:36

You are quite welcome.)

Just be careful that, if the format is not exactly the way you described - even an extra space in the parts that we are seeking, that can throw off the logic.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources