2

(SSIS topic) I have a Full Name column containing names with middle initial and names without middle initial. How can i get the middle initial and put it in the Middle Initial column. How can I solve this using SSIS derived column.

|Middle Initial  |Full Name         |
|                |Carl T. Simpson   |
|                |Karen Thompson    |
|                |Donald J. Clinton |
|                |Samantha Shelby   |

This is the outcome that i want to achieve.

|Middle Initial      |Full Name         |
|T.                  |Carl T. Simpson   |
|                    |Karen Thompson    |
|J.                  |Donald J. Clinton |
|                    |Samantha Shelby   |
Hadi
  • 36,233
  • 13
  • 65
  • 124
datanewbie
  • 29
  • 3
  • Try this answer: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/de0509a0-d27a-4d1a-bedf-02691ce6159d/separate-first-name-mid-name-and-last-name-in-ssis-expression?forum=sqlintegrationservices – Benzi Jun 21 '20 at 05:49
  • @Benzi I tried those codes before I post this question, I doesn't work because the column contains names w/ and w/o middle name. If this question is easy its probably answered correctly by now. thanks – datanewbie Jun 22 '20 at 11:06

1 Answers1

0

You can simply solve this problem using the following expression:

TOKENCOUNT(@[Full Name]," ") > 2 ? TOKEN(@[Full Name]," ",2) : ""

If you are using a previous version of SQL Server 2012, you can try this expression (not sure if it will work for if the middle name is not included):

LTRIM(RTRIM(SUBSTRING(@[Full Name],FINDSTRING(@[Full Name]," ",1),FINDSTRING(@[Full Name]," ",2) - FINDSTRING(@[Full Name]," ",1))))
Hadi
  • 36,233
  • 13
  • 65
  • 124