CODEWITHSUNDEEP
sqlstringsharepointssisexpression
gberisha
gberisha

Reputation: 301

SSIS: Trim field for specific part of string

My Situation:

I have an Employee field which I am getting through a SharePoint list. The current value I am getting is this:

EmployeeID;Employee Firstname Employee Lastname

Say for example:

43;Stacky Stackerflow

What I Need is either the ID alone without the ; or the First and Lastname, but I have no way of telling if the ID is gonna be 1, 2 or 3 Digits Long or in anyway tell how big the Names are gonna be. Is there a way to cut these using any of the Tools in SSIS ? If so how ? And if not, is there another way?

Upvotes: 0

Views: 908

Answers (3)

Hiram
Hiram

Reputation: 2679

1.- If all the values in the list are separated by ";" then you just need to import the file with a ";" separator, you won't need to specify any lengths.

2.- If only this field has a ";" then you can use a Derived Column on SSIS, with a SUBSTRING SQL function, similar to this:

    select SUBSTRING('first;second', 1, CHARINDEX(';', 'first;second') - 1) AS First,
    SUBSTRING('first;second', CHARINDEX(';', 'first;second') + 1, 8000) AS SecondV

Replace the string with in the above example "first;second" with your source column name.

Upvotes: 0

TsSkTo
TsSkTo

Reputation: 1420

This is a Copy and paste from my answer here :Split a single column of data with comma delimiters into multiple columns in SSIS

You can use the Token expression to isolate strings delimited by well, delimiters.

TOKEN(character_expression, delimiter_string, occurrence)

so

TOKEN(EmployeeField, ";", 1)

will give you your ID

You can also set up a simple transformation script component. Use your "DATA" column as an input and add as many outputs as you need. Use the split method and you're set.

string[] myNewColumns = inputColumn.split(";");

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Because this is tagged SQL, I'll answer using that syntax:

select left(Employee, charindex(';', Employee) - 1)

If you want this as an integer, then use:

select (case when isnumeric(left(Employee, charindex(';', Employee) - 1)) = 1
             then cast(left(Employee, charindex(';', Employee) - 1) as int)
        end) as EmployeeId

Upvotes: 0

Related Questions