CODEWITHSUNDEEP
sql-serverexcelssisetlderived-column
Joeysonic
Joeysonic

Reputation: 265

ssis substring charindex , split forename and surname

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

Upvotes: 2

Views: 455

Answers (2)

Hadi
Hadi

Reputation: 37368

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

Upvotes: 1

billinkc
billinkc

Reputation: 61259

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.

Remove Text from a String in SSIS derived column

Upvotes: 1

Related Questions