Using SQL to split text in a field

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 2000 Forums

SQL Server Development (2000)

Using SQL to split text in a field

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2005-10-11 : 09:05:26

Hi,

I've got a database which contains details about people. It contains a "name" field which holds first name, surname and sometimes middle names depending on what the user inputted in the form.

I now need to be able to split out the last word in the "name" field and sort the data alphabetically by that word. I started doing this in VB code and it's proving a bit of a nightmare. So I wondered - are there any inbuilt functions in SQL that would allow me to do this through a query rather than having to resort to another programming language?

For clarification, what I've got at the moment as a query result is:

name             
========                      
Simon Soap
Tim Arnold Hatfield
Bob Davis

What I'm after is:

name                   surname       
========               ========
Bob Davis              Davis 
Tim Arnold Hatfield    Hatfield       
Simon Soap             Soap

Cheers,
Matt

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-10-11 : 09:19:02

Reverse the string (Name). Build a NEW string char by char, checking each character until you hit a <space>. Take this new string and reverse it. Voila, you have the surname.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-11 : 09:39:09

[code]
Declare @test table (name varchar(1000))
Insert Into @Test
Select 'Bob Davis' Union All
Select 'Tim Arnold Hatfield' Union All
Select 'Simon Soap'

Select
name,
surname = right(name,charindex(' ',reverse(name))-1)
From @Test
[/code]

Corey