How to Split 1 field into 3 fields 1

[dragony]

Dear helpful SQL experts,
I'm very new to SQL Server, but have to help my team with data cleansing on MS SQL Server 2000.

I've got a set of data that contains a field with Title, Name, Lastname all in one field (ex. Mr. John Smith). I have to split that field into 3 new fields (Title, FName, LName).

I can find the Title using left() like this:
update #Name set [Title]='Mr.' where left([Name],3)='Mr.'

But how would I handle Firstname and Lastname? There is always a space between First and Lastname, but there isn't always a space between Title and Firstname. I don't even know how to detect a space within a string in T-SQL.

Thank you very much in advance for any suggestion,
dragony

 

[hmckillop]

I have put in the temp table and values I used for testing, it wont cover every eventuality but might do what you need.

declare @v_Temp TABLE (name varchar(100), title varchar(10), firstname varchar(25), lastname varchar(25))
Insert into @v_temp (name) values ('Mr.John Smith')
Insert into @v_temp (name) values ('Mrs.Mary Smith')
Insert into @v_temp (name) values ('Mr. Joe Smith')

DECLARE @v_pos_FirstNameStart INT, @v_PosLastNameStart INT
update @v_Temp 
set 
	@v_pos_FirstNameStart= charindex('.', [Name],1 ),
	[Title]=left([NAME], @v_pos_FirstNameStart),	
	@v_PosLastNameStart= charindex(' ', [Name],@v_pos_FirstNameStart + 2 ),
	[FirstName] = ltrim(SUBSTRING([Name],@v_pos_FirstNameStart + 1, @v_PosLastNameStart-@v_pos_FirstNameStart)),
    [LastName] = ltrim(right([NAME], LEN([NAME]) - @v_PosLastNameStart))

- Just replace the @v_Temp with your table name and it should work

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[dragony]

hmckillop,

Thank you so very much. For extremely quick response and for a perfect solution. The code worked perfectly on the first trial (after making changes as you suggested, of course).

Thank you very much again,
dragony

 

[SQLSister]

Check to make sure your data converted properly in all cases. If there are middle names (Or two word first names that are common in the US South like Mary Jo) or last names with a space (like Saint James) or additional items like MD or III after the name you want to make sure these records also converted properly.

 

[Wyldcard9]

I am trying to use that sample code for something similiar. What am I doing wrong?

declare [Pension Forms].[dbo].[XFRFUND10] TABLE (name varchar(100), firstname varchar(15), lastname varchar(30), initial varchar(5))


DECLARE @v_pos_FirstNameStart INT, @v_PosLastNameStart INT
update [Pension Forms].[dbo].[XFRFUND10]
set
@v_pos_FirstNameStart= charindex(' ', [Name],1 ),
[FirstName]=left([NAME], @v_pos_FirstNameStart),
@v_PosLastNameStart= charindex(' ', [Name],@v_pos_FirstNameStart + 1 ),
[FirstName] = ltrim(SUBSTRING([Name],@v_pos_FirstNameStart, @v_PosLastNameStart-@v_pos_FirstNameStart)),
[Initial] = ltrim(right([NAME], LEN([NAME]) - @v_PosLastNameStart))

I get this error message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

I have tried it with and without the TABLE word in the first line. Either way I get an error.

 

[SQLSister]

Are you trying to use a table variable? VAraibles start with @, try @XFRFUND10 instead of [Pension Forms].[dbo].[XFRFUND10]

Also you have no records in the table so of course you can;t update them. You might need to do an insert instead.

 

[Wyldcard9]

Currently I am here:

declare [Pension Forms].[dbo].[XFRFUND10] TABLE (memberfullname varchar(30), lastname varchar(30), firstname varchar(15))
Insert into [Pension Forms].[dbo].[XFRFUND10] (LASTNAME) values ('Smith John')
Insert into [Pension Forms].[dbo].[XFRFUND10] (FIRSTNAME) values ('Smith Mary')

DECLARE @v_pos_FirstNameStart INT
DECLARE @v_pos_LastNameStart INT

update [Pension Forms].[dbo].[XFRFUND10]
set
@v_pos_LastNameStart= charindex(' ', [memberfullName],1 ),
[LastName]=left([MEMBERFULLNAME], @v_pos_LastNameStart),
@v_pos_FirstNameStart= charindex(' ', [MemberfullName],@v_pos_LastNameStart + 2 ),
[FirstName] = ltrim(right([MEMBERFULLNAME], LEN([MEMBERFULLNAME]) - @v_pos_LastNameStart))


Maybe I am not comprehending. I am trying to split a name field on my table. XFRFUND10 is the table, I am trying to split the MEMBERFULLNAME column into FIRSTNAME, and LASTNAME columns. Am I mixing up tables? I tried the @XFRFUND10, and it parses, but then gets an error:

Server: Msg 536, Level 16, State 1, Line 8
Invalid length parameter passed to the substring function.
The statement has been terminated.

 

[SQLSister]

If it is an existing table why are you declaring it as a table variable? take out the declare statement and try again.

 

[Wyldcard9]

SQLSister,

Thanks I am still learning this(and have a long way to go). That makes sense.

Currently here:
DECLARE @v_pos_FirstNameStart INT
DECLARE @v_pos_LastNameStart INT

UPDATE [Pension Forms].[dbo].[XFRFUND10]
SET
@v_pos_LastNameStart= charindex(' ', [MEMBERFULLNAME],1 ),
[LASTNAME]=left([MEMBERFULLNAME], @v_pos_LastNameStart),
@v_pos_FirstNameStart= charindex(' ', [MEMBERFULLNAME],@v_pos_LastNameStart + 2 ),
[FIRSTNAME] = ltrim(right([MEMBERFULLNAME], LEN([MEMBERFULLNAME]) - @v_pos_LastNameStart))

With this error:
Server: Msg 536, Level 16, State 1, Line 4
Invalid length parameter passed to the substring function.
The statement has been terminated.

I don't see Msg 536 in the Help files. I tried changing the +2 to a +1 with no difference in outcome.

 

[Wyldcard9]

I found the error code listings, but it does not honestly help me.

 

[hmckillop]

I dont see any problem, even tried your statement and it works fine.

I tried this:

create table XFRFUND10
(
MEMBERFULLNAME varchar(50),
LASTNAME varchar(25),
FIRSTNAME varchar(25)
)

insert into XFRFUND10 (memberfullname) values ( 'Joe Bloggs1')
insert into XFRFUND10 (memberfullname) values ( 'Joe Bloggs2 ')
insert into XFRFUND10 (memberfullname) values ( ' Joe Bloggs3')
insert into XFRFUND10 (memberfullname) values ( ' Joe Bloggs4 ')
insert into XFRFUND10 (memberfullname) values ( '   Joe Bloggs5                         ')

--delete XFRFUND10 

DECLARE @v_pos_FirstNameStart INT
DECLARE @v_pos_LastNameStart INT


UPDATE [XFRFUND10] 
SET 
	 @v_pos_LastNameStart= charindex(' ', ltrim(rtrim([MEMBERFULLNAME])),1 ),
    [LASTNAME]=left(ltrim(rtrim([MEMBERFULLNAME])), @v_pos_LastNameStart),    
    @v_pos_FirstNameStart= charindex(' ', ltrim(rtrim([MEMBERFULLNAME])),@v_pos_LastNameStart + 1 ),
    [FIRSTNAME] = ltrim(right(ltrim(rtrim([MEMBERFULLNAME])), LEN(ltrim(rtrim([MEMBERFULLNAME]))) - @v_pos_LastNameStart))

select * from XFRFUND10

Does this work with you?

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.