seperate name from one field into two fields 1

[sbcsandhu]

I have a field called case_name the fields returns last name, first name.
how can i seperate the field into two fields one first name and otehr field last name

this is how its looks now

case_name
White Delrisha
or
Middlebrooks, Ashley

sometimes there is no comma

 

[PatriciaObreja]

This will separate the_name field, into 2 fields for First and Last Name.

the_name must be replaced with replace(replace....)) function.

SELECT the_name,
SUBSTRING(the_name, 1, CHARINDEX(' ', the_name)-1) AS FirstName,
SUBSTRING(the_name, CHARINDEX(' ', the_name), len(the_name)) AS LastName
FROM TableName

 

[sbcsandhu]

cool its works
one last thing

there is a space in my last name
ever last name has a space before the first letter
can i get rid of that?

 

[PatriciaObreja]

Correction:

SELECT the_name,
SUBSTRING(the_name, 1, CHARINDEX(' ', the_name)-1) AS FirstName,
SUBSTRING(the_name, CHARINDEX(' ', the_name) + 1, len(the_name)) AS LastName
FROM TableName

 

[sbcsandhu]

thanks,
last thing promise

when i have a person's name like this Flores Carmen E.

my last name is Carmen E. can i have it only with carmen?

 

[sbcsandhu]

any way of doing this?

 

[PatriciaObreja]

If we replace the second SUBSTRING with:

SUBSTRING(SUBSTRING(the_name, CHARINDEX(' ', the_name) + 1, len(the_name)),
1,
CHARINDEX(' ', SUBSTRING(the_name, CHARINDEX(' ', the_name) + 1, len(the_name)))-1)

it will work only for records like "Flores Carmen E.", but will give an error for the others.

 

[sbcsandhu]

is there a way to make it work for both?

 

[AlexCuse]

Yes you can, but for the love of Bob just clean your data. You've already got the problem of multiple/missing spaces messing everything up, comma's or no comma's, and all this other mess, and look what a tangled pile of garbage you have now for a query. Do you really want to make that worse?

You need to find a way to clean this data (to at least get rid of the comma's and missing spaces problems) as it comes in, if not put it directly into the logical columns (First and Last names).

If you insist on continuing down this half-something'd path, then here:

declare @cms1 table (case_name varchar(100))

insert @cms1 
select 'Brady Tom'
union all select 'Manning Eli'
union all select 'Sc Bsandhu K.'

select left(case_name, charindex(' ', case_name) -1) as LastName
	, case when len(case_name) - len(replace(case_name, ' ', '')) > 1
		then substring(case_name
			, charindex(' ', reverse(case_name), charindex(' ', reverse(case_name)))+1
			, len(case_name) - charindex(' ', reverse(case_name)) - charindex(' ', reverse(case_name), charindex(' ', reverse(case_name))))
		else right(case_name, charindex(' ', reverse(case_name)) -1) end as FirstName
from @cms1

Assumes you have gotten the data into only 2 formats:

Brady Tom F.
Manning Eli

But do you really want to subject whoever takes your place to debugging this monstrosity?

Hope this Helps,

Alex

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[PatriciaObreja]

Replace the second SUBSTRING with this:

CASE CHARINDEX(' ', SUBSTRING(the_name, CHARINDEX(' ', the_name) + 1, len(the_name)))-1
WHEN -1 THEN
SUBSTRING(the_name, CHARINDEX(' ', the_name) + 1, len(the_name))
ELSE
SUBSTRING(SUBSTRING(the_name, CHARINDEX(' ', the_name) + 1, len(the_name)),
1,
CHARINDEX(' ', SUBSTRING(the_name, CHARINDEX(' ', the_name) + 1, len(the_name)))-1)
END

 

[xlbo]

As AlexCuse has already mentioned. If you want to do this in SQL, that is a post for the SQL Server Programming forum as it has nothing to do with Reporting Services.

Please post in appropriate forums

and I'd also echo Alex's pleas to clean your data up !!

Almost always better to clean data 1st, then report on it rather than try to deal with messy data on a repeat basis

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question