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.
Author |
Topic |
bendertez
Yak Posting Veteran
94 Posts |
Posted - 2008-02-20 : 06:47:12
|
HiI 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.eDEREKJOHN PAULbut i need to split the middle name into seperate parts also to give me:DEREKJOHNPAULI'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 LastNameFROM Table |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-02-20 : 08:27:36
|
Also search for split function hereMadhivananFailing to plan is Planning to fail |
|
|
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 |
|
|
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 LastNameFROM Table
What about titles likeDr. John SmithRev. John AndersonPeter Omaha Jr. E 12°55'05.25"N 56°04'39.16" |
|
|
bendertez
Yak Posting Veteran
94 Posts |
Posted - 2008-02-20 : 11:01:12
|
Titles are held in a seperate field - Dr, Mr, Rev etcJr wouldn't be recorded. |
|
|
ranganath
Posting Yak Master
209 Posts |
Posted - 2008-02-21 : 06:11:55
|
Hi,Try This alsoDeclare @Text Varchar(100),@delimiter NVARCHAR(5)Set @Text = 'Deepak john Dev'set @delimiter = ' 'Declare @textXml XmlSelect @TextXml = Cast('<d>' + Replace(@Text, @Delimiter,'</d><d>') + '</d>' As Xml );SELECT T.split.value('.', 'nvarchar(max)') AS dataFROM @textXML.nodes('/d') T (split) |
|
|
|
|
|
|
|