Splitting text by word - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Splitting text by word

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-17 : 07:51:21

I have a table with three columns:


Forename    MiddleName Surname
--------    ---------- -------
John        Robert     Smith
David Craig            Robinson
Eric Ian               Nicholls

Is there a way I can test the first (Forename) field to see if it contains two strings of characters (words) which are each larger than 1 character and then update the table so that the first column is replaced with the first 'word' and the second column is updated with the second 'word'?

This would then return:


Forename    MiddleName Surname
--------    ---------- -------
John        Robert     Smith
David       Craig      Robinson
Eric        Ian        Nicholls

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-17 : 08:09:26

try this
declare @tab table (Forename varchar(32), MiddleName varchar(32), Surname varchar(32))
insert into @tab select 'John','Robert','Smith' union all
select 'David Craig',null,'Robinson' union all
select 'Eric Ian',null,'Nicholls'

select * from @tab

update @tab
set middlename = right(forename,charindex(' ',reverse(forename),1)),
forename = left(forename,charindex(' ',forename,1))
where charindex(' ',forename,1) > 0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-17 : 08:12:52

To avoid leading space

set middlename = right(forename,charindex(' ',reverse(forename),1)-1),

Madhivanan

Failing to plan is Planning to fail

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-17 : 08:24:52

Many thanks guys. I learned sonething new from that :)

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-02-17 : 08:30:23

quote:
Originally posted by OldMySQLUser

...which are each larger than 1 character...

So you want to leave middle initials in the first name column?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-17 : 08:40:34

quote:
Originally posted by blindman

quote:
Originally posted by OldMySQLUser

...which are each larger than 1 character...
So you want to leave middle initials in the first name column?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

No. But I'm trying to clean up a database table littered with garbage input. I have stuff like 'John & Sue' in the Forename field

.
I'll have to work through this stuff in small, systematic, stages.
Only 7 million records, so I *may* live long enough to do it!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-02-17 : 09:22:17

You may find this useful:
http://sqlblindman.pastebin.com/f68f37c15

For example:


declare @tab table (Forename varchar(32), MiddleName varchar(32), Surname varchar(32))
insert into @tab
select 'John','Robert','Smith'
union all select 'David Craig',null,'Robinson'
union all select 'Eric Ian',null,'Nicholls'
union all select 'J. Frederick',null,'Peterson III'
union all select 'Mrs. Juliet','Anne','Von Kaput'
union all select 'Captain James T. Kirk', null, null
union all select 'Clinton, William', 'Jefferson', null

select	dbo.FormatName(coalesce(Forename, '') + ' ' + coalesce(MiddleName, '') + ' ' + coalesce(Surname, ''), 'F') as FirstName,
		dbo.FormatName(coalesce(Forename, '') + ' ' + coalesce(MiddleName, '') + ' ' + coalesce(Surname, ''), 'M') as MiddleName,
		dbo.FormatName(coalesce(Forename, '') + ' ' + coalesce(MiddleName, '') + ' ' + coalesce(Surname, ''), 'L') as LastName
from	@tab

Output:


FirstName	MiddleName	LastName
John		Robert	Smith
David		Craig		Robinson
Eric		Ian		Nicholls
J		Frederick	Peterson
Juliet	Anne		Von Kaput
James		T		Kirk
William	Jefferson	Clinton

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-18 : 04:10:17

That is very useful blindman. Many thanks for taking the time and trouble to help. Much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources