Splitting one column into multiples

Question

Splitting one column into multiples

Darren Harrison

Grasshopper

Points: 10
More actions
January 16, 2007 at 8:51 am

#174563

Hi all
This is my first attempt at this so please bare with me .
I have been given an extract from MOM 2005 which we use as an alerting and shows events from an event log, unfortunatly SQL is producing one string or text with carrage returns in it. when you copy one cell from the table you get multiple lines in an excel spreadsheet.
What i am asking the forum. is there a way to split a column into multiples using the Carrage return as a column delimiter?
Thanks Darren

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173083 More actions · Answer 1

Absolutely. The key is knowing what the carriage return character looks like (which I, unfortunately, don't).

You can do this one of two ways. 1) Use an SSIS package to pull the data into a DataSet destination and use a Script Task to pull it apart. 2) Pull it into SQL Server the way it is, then, using a Temp table, use PatIndex to find each occurance of your carriage return. Once you have that, you can split the data on each of those spots using SubString to start at the position after the CR and continue to the position before the next CR.

Either way, you'll have to do some looping. I recommend counting the CRs and setting them as your WHILE loop counter, then loop through that way instead of using a CURSOR.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 2

It's char(13)

select 'This is a string with a CR

just before just'

, charindex( char(13), 'This is a string with a CR

just before just')

, substring( 'This is a string with a CR

just before just', 1, charindex( char(13), 'This is a string with a CR

just before just'))

, substring( 'This is a string with a CR

just before just', charindex( char(13), 'This is a string with a CR

just before just'), 100)

Brandie Tarvin SSC Guru Points: 173083 More actions · Answer 3

Steve,

You da Bomb! @=)

Darren, do a PatIndex search on the char(13) that Steve recommends and that will get you started. I recommending creating your multiple columns in a Temp table before importing them into your real table rather than updating the real table and locking for the duration of the split code.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Darren Harrison Grasshopper Points: 10 More actions · Answer 4

Darren Harrison

Grasshopper

Points: 10

January 18, 2007 at 5:09 am

#683959

Hi

Many thanks for your Help on this one.

Cheers

Brandie Tarvin SSC Guru Points: 173083 More actions · Answer 5

NP. Let us know if you need any more help.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.