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 a string in to 3 parts

Author  Topic 

bendertez
Yak Posting Veteran

94 Posts

Posted - 2008-02-20 : 06:47:12
Hi

I need to split a string field in to 3 parts.

Currently it is one long first name field and has data like:

DEREK JOHN PAUL

contained in it.

I can split the field up into Firstname and Middlename i.e

DEREK
JOHN PAUL

but i need to split the middle name into seperate parts also to give me:

DEREK
JOHN
PAUL

I'm struggling to pick out the 2nd charindex ' ' in my code.

This is what I have so far that just splits Firstname and middlename:


CASE WHEN forenames LIKE '% %' THEN (substring(forenames, 0, (charindex(' ',
forenames) + 0))) ELSE forenames END "Firstname",

CASE WHEN p_forenames LIKE '% %'
THEN substring(forenames, (charindex(' ',forenames) + 1), 100)
ELSE ''
END AS Middlename,


Anybody have any idea how this can be achieved?

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 06:52:33
If your name always contain three parts use:-
select PARSENAME(REPLACE(NameField,' ','.'),3) AS FirstName,
PARSENAME(REPLACE(NameField,' ','.'),2) AS MiddleName,
PARSENAME(REPLACE(NameField,' ','.'),1) AS LastName
FROM Table
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-20 : 08:27:36
Also search for split function here

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-20 : 08:34:44
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56499&SearchTerms=FormatName


"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-20 : 10:32:07
quote:
Originally posted by visakh16

If your name always contain three parts use:-
select PARSENAME(REPLACE(NameField,' ','.'),3) AS FirstName,
PARSENAME(REPLACE(NameField,' ','.'),2) AS MiddleName,
PARSENAME(REPLACE(NameField,' ','.'),1) AS LastName
FROM Table

What about titles like
Dr. John Smith
Rev. John Anderson
Peter Omaha Jr.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

bendertez
Yak Posting Veteran

94 Posts

Posted - 2008-02-20 : 11:01:12
Titles are held in a seperate field - Dr, Mr, Rev etc

Jr wouldn't be recorded.
Go to Top of Page

ranganath
Posting Yak Master

209 Posts

Posted - 2008-02-21 : 06:11:55
Hi,

Try This also

Declare @Text Varchar(100),@delimiter NVARCHAR(5)
Set @Text = 'Deepak john Dev'
set @delimiter = ' '
Declare @textXml Xml
Select @TextXml = Cast('<d>' + Replace(@Text, @Delimiter,'</d><d>') + '</d>' As Xml );
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)
Go to Top of Page
   

- Advertisement -