Brilliant thanks

so this takes into in consideration if the name field is missing a title.

Ok cheers, is this what you mean?

DECLARE dbo.AccountNumber TABLE (AccountNo ,Name ,pos ,pos)

INSERT INTO dbo.AccountNumber VALUES

(60000000,'Null','Null','Null')

('Null',Gillespie,'Null', 'Null'),

(60000061,'Null','Null','Null'),

('Null','Null','Null','Null'),

('Null', Jacqulyn FamilyNess, 9 ,0),

(60000322, Elroy Ravenclaw19, 6 ,0),

SELECT

AccountNumber,

Name,

space1.pos,

space2.pos

--Title= LEFT(Name,space1.pos-1),

--Forename= SUBSTRING(Name,space1.pos+1,space2.pos-space1.pos-1),

--Surname=...

sorry this is not working still its only applying it to some of the rows how do I create a ddl script to export the data?

Thanks for your help I have been able to get Chris's sql to work,

Thanks for all your help.

I'v attached an Excel file with test data

Many Thanks

Andy

SELECT

AccountNumber,

Name,

Name = LEFT(Name,space1.pos-1),

Name = SUBSTRING(Name,space1.pos+1,space2.pos-space1.pos-1),

Name = SUBSTRING(Name,space2.pos+1,40)

FROM dbo.AccountBase

CROSS APPLY (SELECT CHARINDEX(' ', Name,1)) space1 (pos)

CROSS APPLY (SELECT CHARINDEX(' ', Name,space1.pos+1)) space2 (pos)

But the above creates an error

Msg 537, Level...

SELECT AccountNumber, AccountName

From dbo.Account

Field 1 =

AccountNumber

200000003

Field 2 =

AccountName

Mr Peter ASHWORTH

I want to split the Account Name into three new fields Title, First_Name, Last_Name but Keep both the original fields (AccountNumber...

Yes they would be so example:

Full Name

MR Peter ASHWORTH

would end up as

Full Name TitleFirst NameLast...

Thanks you for your reply!

I do not want to drop the table but split the name on to the same table in new columns?

🙂

Yes Brilliant thank you!!

ContId CreditL CreatedOn ...