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

 

[sbcsandhu]

i saw this post and it seems like it worked for most of the time
thread1462-1342636
but the problem is
left([case name], patindex('%,%',[case name])-1)

sometimes isnt a comma and just a space, is there a way i can tell this function to look at the field 2 or 3 different ways to seperat the name

 

[PatriciaObreja]

If it does not contain ",", we should look for space character.

if patindex('%,%',[case name]) > 0
left([case name], patindex('%,%',[case name])-1)
else
left([case name], patindex('% %',[case name])-1)

 

[ptheriault]

This will work.

DECLARE @myname varchar(400)
SELECT @myname = 'Paul Theriault'

SELECT SUBSTRING(@myname,1,CHARINDEX(' ',@myname)-1)
      ,SUBSTRING(@myname,CHARINDEX(' ',@myname),len(@myname))

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[sbcsandhu]

select

DECLARE @myname varchar(400)
SELECT @myname = [case name]

SELECT SUBSTRING(@myname,1,CHARINDEX(' ',@myname)-1)
,SUBSTRING(@myname,CHARINDEX(' ',@myname),len(@myname))

from cms1

what did i do wron?

 

[ptheriault]

I used a variable for my example, you need to replace my variable with your column name.

SELECT SUBSTRING(@myname,1,CHARINDEX(' '[case name]
)-1)
      ,SUBSTRING(@myname,CHARINDEX(' '[case name]
),len[case name]
))
from cms1

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

I missed a few.

SELECT SUBSTRING[case name],1,CHARINDEX(' '[case name]
)-1)
      ,SUBSTRING[case name],CHARINDEX(' '[case name]
),len[case name]
))
from cms1

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[AlexCuse]

I think you should do this on the client side (in your report). T-SQL is not very good at parsing strings.

For first name:

=Left(Fields!fullname.Value, InStr(Fields!fullname.Value, " ") - IIf(InStr(Fields!fullname.Value, ",") > 0, 2, 1))

For last name:

=Mid(Fields!fullname.Value, InStr(Fields!fullname.Value, " ") + 1)

Hope this helps,

Alex

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

My Crummy Web Page

 

[AlexCuse]

Of course, your field would be named case_name, not fullname ;-)

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

My Crummy Web Page

 

[sbcsandhu]

im still getting an error

select

Left(Fields![case name].Value, InStr(Fields![case name].Value, " ") - IIf(InStr(Fields![case name].Value, ",") > 0, 2, 1))

from cms1


Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '!'.

 

[AlexCuse]

you only want to do this for your query to generate data set:

select case_name from cms1

The expressions I gave you would be used to assign the values to text boxes (or whatever) in your report itself.

Hope this helps,

Alex

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

My Crummy Web Page

 

[sbcsandhu]

still not working

DECLARE @myname varchar(400)
SELECT @myname = [case name]




SELECT SUBSTRING[case name],1,CHARINDEX(' '[case name]
)-1)
,SUBSTRING[case name],CHARINDEX(' '[case name]
),len[case name]
))
from cms1



Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'case name'.

 

[sbcsandhu]

i dont want to do it in the report, i need to link off this names, so i need to do it in the sql side

 

[AlexCuse]

Fair enough, though that makes it more of a T-SQL question than a reporting question ;-)

Take a look at this:

declare @cms1 table (case_name varchar(100))

insert @cms1 
select 'Brady Tom'
union all select 'Manning, Eli'


select left(replace(case_name, ',', '')
		, charindex(' ', replace(case_name, ',', '')) -1) as LastName
	, right(case_name
		, charindex(' ', reverse(case_name))-1) as FirstName
from @cms1

All you really need to do is strip out the commas before attempting to split the string.

Hope this helps,

Alex

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

My Crummy Web Page

 

[sbcsandhu]

im almost there

this query worked


declare @cms1 table ([case name] varchar(100))



select top 103 left(replace([case name], ',', '')
, charindex(' ', replace([case name], ',', '')) -1) as LastName
, right([case name]
, charindex(' ', reverse([case name]))-1) as FirstName
from mrextract.dbo.cms1


when i run it for all records i get an error on record 104

Taggart,Capricia

this person doesnt have a space after the comma
can i alter the code above to make names with no space after the comma

 

[AlexCuse]

sure, but it gets pretty ugly (btw, you don't need to declare @cms1 table, that was only for the test).

It would be

replace(replace(replace([case name], ',', ', '), '  ', ' ')',', '')

(not tested)

You need to first replace comma with comma + space.
Then replace space + space with single space.
Then replace comma + space with single space.

Then do all the other stuff.

If your data is that dirty, probably best to just run an update on the table to clean it up, no?

Hope this helps,

Alex

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

My Crummy Web Page

 

[sbcsandhu]

like this?

select top 95 [case name],
replace(replace(replace([case name], ',', ', '), ' ', ' ')',', ''),
left(replace([case name], ',', '')
, charindex(' ', replace([case name], ',', '')) -1) as LastName
, right([case name]
, charindex(',', reverse([case name]))-2) as FirstName,*
from mrextract.dbo.cms_cws1



im getting an error

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.

 

[sbcsandhu]

ok this works for the top 95 records


select top 95 [case name],
replace(replace(replace(replace([case name], ',', ', '), ' ', ' '),',', ''),'', ' '),
left(replace([case name], ',', '')
, charindex(' ', replace([case name], ',', '')) -1) as LastName
, right([case name]
, charindex(',', reverse([case name]))-2) as FirstName,*
from mrextract.dbo.cms_cws1



but on record 96 i have a name

Felicia Lopez
and its not working on that name

 

[sbcsandhu]

with just this i get all the rows to return



select [case name],
replace(replace(replace([case name], ',', ', '), ' ', ' '),',', '')

my record looks like this

Buzby, Destiny Buzby Destiny

so now how do i grab the first and last name and seperat them in the same query?

 

[sbcsandhu]

so basically i have all my names like this

Smith Jasmine

what would be the code to seperate that to last and first name in two seprate field