Break Up String in SQL Table

[Scotto13]

I have the following data in a field in my SQL table. I want to break up the data in this field so I can use it in reporting:

Sample records:
Record 1:
Player: (265613) Miss Linlde M Simonds, Promo Adjusted -> 10.00, Beg. Bal -> 0.00, End Bal -> 10.00, FreeSlotPlay

Record 2:
Player: (115814) Mr. Seen J Lifield, Promo Adjusted -> 20.00, Beg. Bal -> 5.00, End Bal -> 25.00, FreeSlotPlay

This is all one field - without carage returns. I want to separate this data into the following fields:
Player#
PlayerName
PromoAdjustedAmt
BeginingBalanceAmt
EndBalanceAmt

Please let me know how to set this up.

I was able to pull the comment - which is the data at the end of these records "FreeSlotPlay" by using this function:

,right((substring(comments,charindex('End Bal ->',comments),40)),(len(substring(comments,charindex('End Bal ->',comments),40))-charindex(', ',(substring(comments,charindex('End Bal ->',comments),40))))) as PBTComment

I am having trouble getting these other fields extracted.
Your help is greatly appreciated.
Scotto13

[cgyrob]

I have question.

How were these records imported into the table in the first place?

It would have been easier to parse them correctly when they were initially loaded.

[Scotto13]

cgyrob,

I am the database analyst and have no control how the data is created. This is unfortunate, because as you mention this data would be much better if it was organized into sep fields in the first place.

[cgyrob]

You should be able to create a stored procedure and try to break off a piece at a time.

If you don't need the data live maybe you can make a dts package to run daily to parse these strings and load them into the new fields.

[Scotto13]

I don't have permissions to create or run SPs.

[cgyrob]

I can't see any easy way to parse this string in a single select statement. I would try each individual piece at a time and then once you can parse each piece put them back together.

You might also look into the PARSENAME function as it might help with the parsing.

Sorry I couldn't help more, maybe someone else might have some suggestions.

[sknake]

Are the sample rows you posted in one column in the mySQL table?

[Scotto13]

I figured out how to extract the data from the fields.

[gbann123]

Similar Problem: I have a field in a table that I want to separate into three fields/columns in a query but I can't figure it out. The data in the table looks like below and I want to separate everything before, between, and after the colon: Any thoughts?

66208-66208:Beige Overflow:One Size Fits All
PADUA-13493:Beige/Khaki: XLarge
8018-74058:Brown:30
8018-74058:Brown:38
8014-74047:474 brown dark:34
8014-74047:474 brown dark:38
7004-79931:413 brown dark:28
7004-79930:Black:28
8007-74036:Black:30
8007-74037:815 green dark:30
8008-74037:815 green dark:32