How to select first name only ?

Question

Post reply

How to select first name only ?

Viewing 3 posts - 1 through 4 (of 4 total)

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

MadAdmin SSChampion Points: 11261 More actions · Answer 1

do something like

select left(Name,charindex(' ',name)) from mytable

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

sgmunson SSC Guru Points: 110651 More actions · Answer 2

MadAdmin (5/30/2015)
do something like
select left(Name,charindex(' ',name)) from mytable

To cover the possibility of no spaces resulting in a NULL value, try this:

WITH mytable AS (

SELECT 'Alok Kumar' AS [Name] UNION ALL

SELECT 'Sunita kuamri' UNION ALL

SELECT 'Rohit Gupta' UNION ALL

SELECT 'OopsyDaisy'

)

SELECT LEFT([Name], ISNULL(NULLIF(CHARINDEX(' ',[Name]), 0), LEN([Name]))) AS First_Name

FROM mytable

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Rich Mechaber SSChampion Points: 11131 More actions · Answer 3

Since no one mentioned this, I'll jump in: think seriously about restructuring your database table and application to provide and store first name separately from last name. You don't want to be parsing names every time you need to query them.

Also, you'll always have parse challenges if you leave things as is: what is Mary Anne Bosworth's first name? If you parse on a space, you'll incorrectly return Mary. Is Mary her first name and Anne a middle name? Is her first name "Mary Anne"? You can't tell, no matter how cleverly you parse your data.

You can't index on last name using a full name column, which is a common way applications look up people, reports sort people, etc. Performance will suffer.

If you can, I'd fix your data structure problem rather than working around it.

Rich