Find first name?

Question

Post reply

Find first name?

jenniferhu46

SSCrazy

Points: 2990
More actions
December 9, 2003 at 1:12 pm

#81737

Hi,
I have a Manager_Name field which contains first and last name (example: 'Pam Smith').
How can I write a query to list only first name?
Thank you.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

SELECT LEFT(Manager_Name, CHARINDEX(' ', Manager_Name, 1) -1)

* Noel

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

I bet Jonathan will post PARSENAME stuff.

In the meantime try



SELECT LEFT(your_field,CHARINDEX(' ', your_field))

this assumes that you always have a blank as separator and no leading blanks.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Yup,



SELECT LEFT(your_field,CHARINDEX(' ', your_field,1)-1)

is better

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jonathan SSC-Insane Points: 20427 More actions · Answer 4

quote:
I bet Jonathan will post PARSENAME stuff.
Frank

No, Noel's way is better than PARSENAME for this as it ignores multiple word last names (like mine). Of course, some people down South have multiple word first names.

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

quote:
Hi,
I have a Manager_Name field which contains first and last name (example: 'Pam Smith').
How can I write a query to list only first name?
Thank you.

Btw, any chance to change table design and split the information?

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

RD Francis SSCarpal Tunnel Points: 4867 More actions · Answer 6

quote:
... it ignores multiple word last names (like mine). Of course, some people down South have multiple word first names.
--Jonathan

Which is why, when you're asked to do something like this, you make sure you explain what the results will be to the individual motivating the request.

I normally enter "R David" as my first name in forms, and am mildly peeved when that simply gets shortened to "R" (note: I have never lived further south than my current residence of Columbus Ohio 🙂 ).

If a name has three distinct words, all at least two characters long, it is impossible to guess 100% accurately for all valid names the right way to break up the data.

Where can I see ParseName? I'd like to see how others have dealt with the issues involved.

R David Francis

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Jonathan came up with a great solution to a different problem.

Unfortunately I lost track of this thread, but you can always look at BOL

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 8

Here is the answer to the multiple word firstname problem

declare @name varchar(100)

set @name = 'Marie Sarah Vigote'

SELECT LEFT(@name, LEN(@name)-CHARINDEX(' ', REVERSE(@name), 1))

Of cours is you have a name where you have multiple word first name AND multiple word lastname then you have a problem ...

But I thing it is more frequent to have more the one firstname then more then one lastname (of cours Jonathan is an exception, as usual)

Bye

Gabor

Bye
Gabor