Re: String manipulation - taking one long csv string and putting into separate record fields

From: Russell Fields (RussellFields_at_NoMailPlease.Com)
Date: 09/06/04 

Date: Mon, 6 Sep 2004 10:06:35 -0400

Keith,

You could try something like this:

DECLARE @MyVariable NVARCHAR (1000)

SET @MyVariable = 'Insert Into MyTable Values (''' + Replace(@SQLMailText,
',', ''',''') + ')' -- All single quotes

EXEC (@MyVariable)

You would, of course, need to avoid breaking down a message string that has
commas embedded in it.

Russell Fields

"Keith Langmead" <klangmead@nospam.kms.co.uk> wrote in message
news:OOPcy1AlEHA.1180@TK2MSFTNGP14.phx.gbl...
> I'm using SQLMail to pull in e-mails which contain a load of csv formatted
> text, in this particular case it is the output from the event log.
>
> I can get SQL to see the text content, but for ease I want to split up the
> text into it's respective sections, and put each line into its own record,
> with each part of the line in its own record. For example, below you can
see
> the information from one line :
>
> 2/17/2004,10:52:48,8,1,517,Security,NT AUTHORITY\SYSTEM,,Server-01,SYSTEM
> NT AUTHORITY (0x0,0x3E7) administrator Domain-net (0x0,0xA4DDB69)
> 2/17/2004,10:52:48,8,4,578,Security,Domain-net\Administrator,,Server-01,Ev
> entLog 0 248 Server-01$ Domain-net
>
> What I want to end up with is a record for the fields split up :
>
> Date : 2/17/2004
> Time : 10:52:48
> Category : 8
> Type : 1
> EventID : 517
> Source : Security
> User : NT AUTHORITY\SYSTEM
> Computer : Server-01
> Description : SYSTEM NT AUTHORITY (0x0,0x3E7) administrator Domain-net
> (0x0,0xA4DDB69)
>
> Can anyone give me an idea of the best way to do something like this, or
> which commands would be required so I can at least hunt through BOL with
> some idea of what I'm looking for.
>
> Thanks
> Keith
>
>