May 30, 2015 at 4:04 am
Hi,
There is a column for Name with data type varchar 50 the input data is like Alok Kumar, Sunita kuamri, Rohit Gupta Like that. The column contains the data as Combination of First name and second name.
I would like to write a select query for selecting first part of name like Alok, Sunit, Rohit and so on only, ignoring the second part of name. Please help me.
May 30, 2015 at 4:25 am
June 1, 2015 at 10:12 am
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)
June 4, 2015 at 2:14 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply