CODEWITHSUNDEEP
ssis
SoupOfStars
SoupOfStars

Reputation: 147

SSIS: Best way to split text?

What would be the best way to split a text column such as this:-

id  | Name               |
--------------------------
 1  | John Doe (Q849yu)  |
 2  | Fred Blogs (f564ju |

To this:-

id  | Name       | PersonalID
--------------------------
 1  | John Doe   | (Q849yu)
 2  | Fred Blogs | (f564ju

I have tried to do this in Derived Column, however I can't seem to get the expression right.

Is there an easier way than derived column?

Upvotes: 3

Views: 311

Answers (2)

Joost
Joost

Reputation: 1913

A substring / findstring expression would be the best solution in this case. Search for " (" with a findstring. Then use that number in a substring expression. Something like this for the first part: SUBSTRING([Name], 1, FINDSTRING([Name], " (", 1))

In SSIS 2012 the TOKEN was introduced. Which could be useful if you have a multi-value column. Last alternative is the Script Component with a .net string split method.

Upvotes: 1

AdamL
AdamL

Reputation: 13191

This will do the trick. Assuming, that searched string is always after last space character:

ltrim(RIGHT( RTRIM( "John Doe (Q849yu) "), FINDSTRING( REVERSE( RTRIM( "John Doe (Q849yu) "))," ",1)))

If you want a better way to do this, especially for more complicated strings, try using Regular Expressions in Script Component. Once you write a component, writing a regex and modifying it is a piece of cake in comparison to messy expressions in Derived Column transformation.

You'll learn more here: Pattern matching in SSIS using Regular Expressions and the Script component

Upvotes: 2

Related Questions