I have an Excel source in SSIS with a column called [Name] which has an entry like Joe Bloggs
I want to separate [Name] into [Forename] and [Surname]
How can I make two derived columns please as it does not work as an SQL statement
As normal SQL would be:
SUBSTRING([Name], 1, CHARINDEX(' ', [Name]) - 1) AS [Forename]
SUBSTRING([Name], CHARINDEX(' ', [Name]) + 1, LEN([Name])) AS [Surname]
CHARINDEX() does not work in derived columns
Please help
Correct, SSIS is a different product from TSQL so you'll need to use language appropriate syntax.
You'll want to add a derived column component to find the first space. The FINDSTRING operator is what you're looking for. FINDSTRING([Name]," ",1) and add that as a new column called SpacePosition
Add a second derived column component after that to allow you to create the forename/surname columns. Syntax here though should match what you've provided
SUBSTRING([Name], 1, [SpacePosition] - 1)
RIGHT([Name], [SpacePosition] + 1)
The reason I advocate for doing this in two steps that if you're getting incorrect results, then you can at least confirm did SpacePosition yield the expected results. Because you may end up with Madonna, Prince, or Lee Harvey Oswald in your input data.
You can use TOKEN() and TOKENCOUNT() functions to split the column as follows:
First name:
TOKEN([Name]," ",1)
Last name:
TOKEN([Name]," ",TOKENCOUNT([Name]," "))
In addition, the FINDSTRING() function is the alternative of SQL CHARINDEX() function in the SSIS expressions. As an example:
FINDSTRING([Name], " ", 1)
References