Ad:

MS SQL Discussion Thread
Unsolved
Views: 15769

Jan 6th, 2007

Splitting Full name data to First, Last

Expand Post »

I'd like to split a full name field on a space into first name last and update my database, I would prefer to stick to TSQL/SQL if it can be done on SQL Server 2005, any ideas?

table actors

actor_id int Unchecked
actor_name varchar(50) Checked
rating smallint Checked
actor_last_name varchar(50) Checked

DECLARE
@position int,
@fullname varchar(50),
@lastname varchar(50)
DECLARE
Names CURSOR FOR
SELECT actor_name, actor_last_name
FROM actors;
OPEN Names;
FETCH NEXT FROM Names INTO @fullname, @lastname;
WHILE @@FETCH_STATUS = 0
BEGIN
set @position=CHARINDEX(" ",@fullname) -- gives Invalid Column name
if(@position>0)
begin
select COUNT(*) from ratings -just needed something here to compile
end
FETCH NEXT FROM Names
END
CLOSE Names
DEALLOCATE Names

SELECT actor_name, actor_last_name
FROM actors;
GO

Similar Threads

will my 180 GB drive lose data on fresh install of Windows XP? in Windows NT / 2000 / XP
C++ and data structure tutorial in C++
conversion of text file into data base file in Visual Basic 4 / 5 / 6
Suggestions for perl resources? Dealing with csv data. in Perl
Corrupted HDD? in Storage

Yamahab

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Jan 2007

Permalink

Jan 7th, 2007

Re: Splitting Full name data to First, Last

Click to Expand / Collapse Quote originally posted by Yamahab ...

I'd like to split a full name field on a space into first name last and update my database, I would prefer to stick to TSQL/SQL if it can be done on SQL Server 2005, any ideas?

table actors

actor_id int Unchecked
actor_name varchar(50) Checked
rating smallint Checked
actor_last_name varchar(50) Checked

DECLARE
@position int,
@fullname varchar(50),
@lastname varchar(50)
DECLARE
Names CURSOR FOR
SELECT actor_name, actor_last_name
FROM actors;
OPEN Names;
FETCH NEXT FROM Names INTO @fullname, @lastname;
WHILE @@FETCH_STATUS = 0
BEGIN
set @position=CHARINDEX(" ",@fullname) -- gives Invalid Column name
if(@position>0)
begin
select COUNT(*) from ratings -just needed something here to compile
end
FETCH NEXT FROM Names
END
CLOSE Names
DEALLOCATE Names

SELECT actor_name, actor_last_name
FROM actors;
GO

Hi Yamahab,
It appears that you are already on the right track. The invalid column name is due to the use of double quotes rather than single quotes - something I do often when writing SQL.
e.g.
set @position=CHARINDEX(' ',@fullname)
should resolve the error

RobStocks

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Jun 2006

Permalink

Jan 9th, 2007

Re: Splitting Full name data to First, Last

Thank you, I guess I'm used to php where I can freely switch.

Yamahab

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Jan 2007

Permalink

Jan 9th, 2007

Re: Splitting Full name data to First, Last

I think you're better off avoiding cursors where possible! It's much slower than a straight up statement, such as:

MS SQL Syntax (Toggle Plain Text)
SELECT name,
   substring(name,1,charindex(' ', actor_name)-1) FirstName,
   substring(name,charindex(' ', actor_name)+1,len(actor_name)) LastName
FROM actors
select name,
   substring(name,1,charindex(' ', actor_name)-1) FirstName,
   substring(name,charindex(' ', actor_name)+1,len(actor_name)) LastName
from actors

to do an update, you could do:

MS SQL Syntax (Toggle Plain Text)
UPDATE actors
  SET first_name = substring(name,1,charindex(' ', actor_name)-1),
      last_name = substring(name,charindex(' ', actor_name)+1,len(actor_name))
update actors
  set first_name = substring(name,1,charindex(' ', actor_name)-1),
      last_name = substring(name,charindex(' ', actor_name)+1,len(actor_name))

MCP

Reputation Points: 14

Solved Threads: 3

Light Poster

Offline

44 posts
since Sep 2006

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Date Problem

Next Thread in MS SQL Forum Timeline: Data Replication

DaniWeb Message

Cancel Changes

User Name
Password