Extracting multiple data from single 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

General SQL Server Forums

New to SQL Server Programming

Extracting multiple data from single column

Author

Topic

tranquilraven
Starting Member

19 Posts

Posted - 2012-06-18 : 11:32:22

I have a single column that has the following data in it:


[Place of Purchase:Walmart USA][Date of Purchase:01/2011][Type Of Pool:Metal Frame Round][Pool Size:20x48][Order Type:Purchase][Problem Code:Pump][Resolution Code:Purchased Item]

[Place of Purchase:Academy Sports][Date of Purchase:01/2011][Type Of Pool:Family Pool][Pool Size:05x15][Order Type:Purchase][Problem Code:Accessory][Resolution Code:Purchased Item]

[Place of Purchase:Academy Sports][Date of Purchase:01/2011][Type Of Pool:Family Pool][Pool Size:05x15][Order Type:Purchase][Problem Code:Accessory][Resolution Code:Purchased Item]

[Place of Purchase:Academy Sports][Date of Purchase:01/2011][Type Of Pool:Family Pool][Pool Size:05x15][Order Type:Purchase][Problem Code:Accessory][Resolution Code:Purchased Item]

I need to extract The data between the [] individually, is there a way to do this? The data between the [] changes and is not the same each time so lengths can vary. The titles stay the same, ie. Place of Purchase:

Is there a way to extract [Place of purchase:(wild card)]

Thank you for any education you can supply me.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-18 : 11:45:53

The simplest I can think of is to use a string splitter function. For example, the one listed in Fig.21 here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Copy that code and run it to install the function. Then, you can do something like this to get the values between the strings

SELECT 
	ItemNumber,
	STUFF(Item,1,1,'') AS String 
FROM
	YourTable t
	CROSS APPLY dbo.DelimitedSplit8K(YourStringColumn,']') c

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-18 : 12:18:07

one of reasons why you need to normalise data onto table fields is to avoid complicated data manipulations like this. Storing then individual data elements in different columns would have made life much easier for you!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tranquilraven
Starting Member

19 Posts

Posted - 2012-06-18 : 12:52:41

Had I been working here when they did this, I could have had a say in it, but since I started here after the database was created, I can only use what is given me.

quote:
Originally posted by visakh16

one of reasons why you need to normalise data onto table fields is to avoid complicated data manipulations like this. Storing then individual data elements in different columns would have made life much easier for you!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-18 : 14:50:07

yep...i understand that. just wanted to reiterate recommended approach

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources