Case statemant- separate forenames

Hi All

I need to separate the forenames within the staff table, at present it looks something like this:

John-Test
John - Test
John - Test
Richard
Bill Jones
John - Test MiddleName
John-Test Mid

I need to split the middlenames out and remove the spaces within the double barrelled names

John-Test
John-Test
John-Test
Richard
Bill
John-Test
John-Test

Thanks in advanced

Rich

hi...

there is a recent post solving a similar issue..have a look...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1073376&SiteID=1

|||

It will do..

Create Table Names
(
FullName varchar(100)
)
Go

Insert Into Names values ('John-Test ')
Insert Into Names values ('John - Test')
Insert Into Names values ('John - Test')
Insert Into Names values ('Richard')
Insert Into Names values ('Bill Jones')
Insert Into Names values ('John - Test MiddleName')
Insert Into Names values ('John-Test Mid')

Go

--Simple / Direct Logic

Select
A Actual
,B + Substring(C,1,CharIndex(' ',C)) Result
From
(
Select
FullName A
,Replace(Substring(FullName,1,CharIndex('-',FullName)),' ','') B
,Ltrim(Substring(FullName,CharIndex('-',FullName)+1,len(FullName)-CharIndex('-',FullName))) + ' ' C
from
Names
) as Data

Go

--Creating Function
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetProperName]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetProperName]
Go

Create Function Dbo.GetProperName(@.Name as varchar(100)) Returns Varchar(100)
as
Begin
Declare @.B as Varchar(100);
Declare @.C as Varchar(100);
Declare @.Result as Varchar(100);

Select

@.B = Replace(Substring(@.Name,1,CharIndex('-',@.Name)),' ','')
,@.C = Ltrim(Substring(@.Name,CharIndex('-',@.Name)+1,len(@.Name)-CharIndex('-',@.Name))) + ' '

Select @.Result = @.B + Substring(@.C,1,CharIndex(' ',@.C))
return @.Result;
End

go
Select FullName, Dbo.GetProperName(fullname) from names
go
Drop Table Names