Might be u get hel with dis.

CREATE TABLE dbo.Test

(

idint,

create_dt DATETIME NOT NULL,

Field1 NVARCHAR(100) NOT NULL,

Field2 NVARCHAR(2500) NOT NULL

);

GO

Insert Test

Select '1','24/Sep/2012','.jim|frank|sue','Alan|Tom|George' union all

Select '2','25/Sep/2012','jim|frank|sue','jim|frank|sue'

Insert Test

Select '1','24/Sep/2012','.jim|frank|sue','Alan|Tom|George' union all

Select '2','25/Sep/2012','jim|frank|sue','jim|frank|sue'

GO

Declare @asd nvarchar(100)

select @asd=Field1 from Test where create_dt='24/Sep/2012'

Select Data as Field1 from Split(@asd,'|')

CreateFUNCTION [dbo].[Split]

(

@RowData nvarchar(2000),

@SplitOn nvarchar(5)

)

RETURNS @RtnValue table

(

Id int identity(1,1),

Data nvarchar(100)

)

AS

BEGIN

Declare @Cnt int

Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)

Begin

Insert Into @RtnValue (data)

Select

Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

Set @Cnt = @Cnt + 1

End

Insert Into @RtnValue (data)

Select Data = ltrim(rtrim(@RowData))

Return

END