CODEWITHSUNDEEP
excelssisexpression
JLone
JLone

Reputation: 115

SSIS Extracting Just A First Name From Full Name

Still new to SSIS, but I've run into a problem while trying to load an excel file into my database. I'm trying to load the first name then last name into my database table, but the excel file is set up a bit weird for some reason.

This is an example of what the excel file looks like Excel File

First Name    Last Name
----------    ---------
Jason         Doe
 Derek M      Smith
 John L       Doe
 Carol        Smith

So, every entry after the first has a blank space in it for some reason, also some entries have a middle initial included in the first name and some do not.

What I'm trying to do is extract just the first name and leave the middle initial out.

What I have it doing currently is just trim the blank space from the excel file which looks like this.

TRIM([Member First Name])

**OUTPUT** 
Jason
Derek M
John L
Carol

When I try to experiment with cutting out the middle initial, I start running into problems. 

 SUBSTRING(TRIM(FULLNAME),1,FINDSTRING(TRIM(FULLNAME)," ",1))
 
 **OUTPUT**
 
 Derek
 John
 ​

So, this way cuts out the initial, but only displays entries who have a middle initial, and leaves entries that do not have an initial, blank.

Moving the TRIM around either leaves the column blank or just displays the first letter of their first name.

What can I do to just extract the first name from this?

Thanks in advance.

Upvotes: 1

Views: 868

Answers (3)

KeithL
KeithL

Reputation: 5594

I'd check the trimmed result to see if 2 spaces back is blank. This avoids complex names like Ann Lee.

substring(  trim([First name]),len(trim([First name]))-2,1)   ==" " ? substring(trim([First name]),1, len(trim([First name]))-3) : trim([First name])

Upvotes: 0

Mehdi Mehdi
Mehdi Mehdi

Reputation: 44

Your derived column expression should be like this :

FINDSTRING(TRIM(FULLNAME)," ",1) == 0 ? FULLNAME : SUBSTRING(TRIM(FULLNAME),1,FINDSTRING(TRIM(FULLNAME)," ",1))

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31775

Wrap your current SUBSTRING... in a ternary expression (aka an in-line IF) so that it only does the SUBSTRING... if there is a space in the TRIMMED value. ELSE it just does the TRIM.

Here is a psuedocode outline of how it should look:

{Condition} ? {Value if True} : {Value if False}

{Condition} is a test that will be TRUE if the trimmed string contains a blank space, and false if it doesn't.   The FINDSTRING() function should be useful here.

{Value if True} can simply be the current SUBSTRING(...) that you have in your last code snippet.

{Value if False} can simply be the TRIM(...) that you have in your second-to-last code snippet.

Upvotes: 1

Related Questions