Extracting Last Name before comma

Question

The name in the file are coming in two different format :

Ahad,Ali Abdul -- Format 1

Doe John, West -- Format 2

I am using the following derived column expression:

FINDSTRING(my column," ",1) != 0 ? (SUBSTRING(my column,1,FINDSTRING(my column," ",1) - 1)) : my column

which gives me "Ahad,Ali" but only "Doe" for format 2 and I need the whole name "Doe John, West"

I am sure I need to add another check not sure how.

Thanks,

Ali.

Answer 1

Hi @Ali Ahad,

Here is an SSIS expression that is working for your sample data.

LEFT(@[User::Var_column] , FINDSTRING(@[User::Var_column], ",", 1)) +
TOKEN(SUBSTRING(@[User::Var_column], FINDSTRING( @[User::Var_column], ",", 1) + 1, 1000), " ", 1)

Answer 2

Hi,

I have tried it like below ,lets see if if helps:

create table #tmp(Pname varchar(30))
insert into #tmp(Pname)
values 
('Ahad,Ali Abdul'),
('Doe John,West'),
('Ahad,Ali')

select 
	Pname = case
	when PATINDEX('%[,]%', Pname)<PATINDEX('%[ ]%', Pname) then substring(Pname,1,PATINDEX('%[ ]%', Pname))
	when PATINDEX('%[,]%', Pname)>PATINDEX('%[ ]%', Pname) then pname
	else Pname
	end 

from #tmp

Answer 3

Hi @Ali Ahad,

What's your expected result?

Ahad,Ali Doe John, West

If yes, it is hard to meet the requirement due to the rules without logic.

If I misunderstand your need, please incorrect me.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.