locked
Derived coulm help RRS feed

  • Question

  • Hi all,

    I have a colum (shoe info) which contains data in the following way

    shoe info

    training shoe (nike) | 45TF

    what i need to do is split off everything to the right of the pipe delimiter and place it in another column, so the data looks like this

    shoe info                       Code

    training shoe (nike)         45TF

    i think the derived column is the way to do this but any help or examples would be greatly appriciated.

    thanks in advance.

    Tuesday, February 7, 2012 12:19 PM

Answers

  • Value is like shoe_info. Please see the example in my first reply.
    In first example i used String as Value and in second example i used Column name as Value.
    • Edited by danimian Tuesday, February 7, 2012 2:22 PM
    • Proposed as answer by Eileen Zhao Thursday, February 9, 2012 1:16 PM
    • Marked as answer by Eileen Zhao Monday, February 13, 2012 2:16 AM
    Tuesday, February 7, 2012 2:20 PM

All replies

  • Hi,

    here you go. After executing this query you will recieve the code as answer then map it to second column.

    select

    substring('training shoe (nike) | 45TF',

    charindex('|','training shoe (nike) | 45TF')+1 ,

    len('training shoe (nike) | 45TF')) as [Code]

    select

    substring(shoe_info,

    charindex('|',shoe_info)+1 ,

    len(shoe_info)) as [Code]

    Kind Regards

    Please vote as helpful or mark as answer, if it helps!

    Cheers

    Dani

    • Edited by danimian Tuesday, February 7, 2012 12:50 PM
    Tuesday, February 7, 2012 12:43 PM
  • Hi

    thanks for the reply however i am new to ssis and not really sure where to run the query you sugested.

    i was under the impression that i needed to do this split using a derived column function from the toolbox.

    is that the correct way in your opinion.

    i have imported the data from an excel flat file.

    Tuesday, February 7, 2012 12:51 PM
  • You can get the values with the following expressions:

    SUBSTRING(value,1,FINDSTRING(value,"|",1) - 1)
    RIGHT(value, LEN(value) - FINDSTRING(value,"|",1))

    You need some tests to make sure that there is a "|".

    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

    • Proposed as answer by Todd McDermid Tuesday, February 7, 2012 9:57 PM
    Tuesday, February 7, 2012 12:52 PM
  • If there's only one column to split into two columns, the derived column with substring will work just fine.
    However, if there are multiple columns, maintenance of the derived column can become cumbersome.

    In that case I would advice to use a script component that utilizes the .NET split function:

    http://msdn.microsoft.com/en-us/library/b873y76a.aspx


    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...

    Tuesday, February 7, 2012 12:55 PM
  • After importing data from Excel file use Derive Column transformation.

    Try this to use this against the column 'Code'. Write this query under the expression.

    Tuesday, February 7, 2012 12:59 PM
  • Hi the the word value which appears in the expresion, what value to I use is it the original column name?

    Thanks

    Tuesday, February 7, 2012 1:11 PM
  • Hi the the word value which appears in the expresion, what value to I use is it the original column name?

    Thanks


    Yes.

    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...

    Tuesday, February 7, 2012 1:13 PM
  • Value is like shoe_info. Please see the example in my first reply.
    In first example i used String as Value and in second example i used Column name as Value.
    • Edited by danimian Tuesday, February 7, 2012 2:22 PM
    • Proposed as answer by Eileen Zhao Thursday, February 9, 2012 1:16 PM
    • Marked as answer by Eileen Zhao Monday, February 13, 2012 2:16 AM
    Tuesday, February 7, 2012 2:20 PM