help writing SUBSTRING() expression please

  • I have strings like this: MFMGOOGLE, inside my [Business Unit] column, from which I need to strip the "MFM". Yet the following Derived Column expression isn't working.

    SUBSTRING([Business Unit], 4, 50).

    The above should retrieve values in the Business Unit column, find the 4th letter, and return it and everything following up to 50 letters (which is more than adequate). But it's not doing that.

    I don't care if the expression evaluates for the "MFM" string explicitly or not, I just want to retain whatever comes after the MFM.

    Can you show me how to write SUBSTRING()?

  • The substring syntax that you are using looks correct to me. What result are you actually getting instead of the expected one?

  • agreeing with Dave, it seems to work fine, but it assumes that MFM always exists and is always those three letters int eh first three chars.

    if we are grabbing something from the middle of an existing string, we need to find the start of MFM instead of assuming the first 3 chars

    I just put this sample data together as an example:, maybe it doesn't always exist? is that it?

    With MySampleData([Business Unit])

    AS

    (

    SELECT 'MFMGOOGLE' UNION ALL

    SELECT 'MFMArnoldDeCaprio' UNION ALL

    SELECT 'MFMBruceSchwarzenegger' UNION ALL

    SELECT 'MFMArnoldDamon' UNION ALL

    SELECT 'MFMLeonardoPitt' UNION ALL

    SELECT 'MFMBruceWahlberg'

    )

    SELECT SUBSTRING([Business Unit], 4, 50) FROM MySampleData

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hi Dave 23, thanks for replying.

    When I run it I just get the whole [Business Unit] string value.

    I have also tried:

    SUBSTRING([Business Unit],4,LEN([Business Unit]) - 3)

    and that returns the whole [Business Unit] string as well.

    The datatype is Unicode (DT_WSTR).

    :crazy:

  • Hi Lowell, you posted while I was replying to Dave23. Thanks.

    Well,

    1. I've got a conditional split diverting only those rows where the destination column is missing a value

    2. Following the conditional split I've got a Derived Column where I house the expression

    2. Between the conditional split and Derived Column I've got Data Viewer enabled so I can view the subset of data that meets the condition and to look if the Business Unit is populated and then to see if replacement for the destination column contains the properly truncated value.

    So, I think I have contained the data pretty well. Plus, it's not like the expression is returning nothing at all, but actually the whole value, so the issue is not null and it is also not that there is no 'MFM' value in the source column because the destination column now contains the MFM as well, rather than minus the MFM.

    Do you have more ideas?

  • This is a stumper. It's a long shot, but is there any chance that there may be unprintable characters preceding the string?

    To be sure, you could try something like:

    SUBSTRING(TRIM([Business Unit]), 1, 3) == "MFM" ? SUBSTRING(TRIM([Business Unit]), 4, 50): [Business Unit]

  • TRIM did the trick.

    thank you very much!!!!!

  • Lowell, I have a select query that I use as the 'architectural plan' for my SSIS package. From this experience I have noticed that because the source of my SSIS package is an Excel spreadsheet, there are many idiosyncracies that I do not have to consider when I am writing a sql statement against a sql table, that I do have to consider (but currently am not good at :-)) when writing for excel.

    I am learning how to troubleshoot excel based ssis flows.

    Thank you for your help today.

  • Glad to hear that it worked. Excel can be a slippery beast. Whenever I'm asked to use it as a data source, I see if it is feasible to export worksheet contents to a .csv file and use that as my source instead. It doesn't always go my way, but it can make life easier.

  • so this is working

    To be sure, you could try something like:

    SUBSTRING(TRIM([Business Unit]), 1, 3) == "MFM" ? SUBSTRING(TRIM([Business Unit]), 4, 50): [Business Unit]

    I would like to evaluate one more condition and wondering if you can help.

    In total I would like to evaluate

    1. if one occurrence of the DT_WSTR string '%COST POOL%' exists in column [Description 01]<-----this is the additional condition

    2. and if it does to proceed with the above string, ie. evaluation of the existence of "MFM" in the first 3 letters of [Business Unit] column

    3. and finally, if that evaluates to true, the extraction of the substring to the right of "MFM" in [Business Unit].

    I've tried this but it doesn't parse:

    FINDSTRING([Description 01], "COST POOL", 1))==1?:(SUBSTRING(TRIM([Business Unit]), 1, 3) == "MFM" ? SUBSTRING(TRIM([Business Unit]), 4, 50): [Business Unit])

    I plan to put it into the Conditional Split transformation. Can you help me with the Expression?

  • Hi,

    For getting the substring except MFM from the SQL Server DB itself,

    With MySampleData([Business Unit])

    AS

    (

    SELECT 'MFMGOOGLE' UNION ALL

    SELECT 'MFMArnoldDeCaprio' UNION ALL

    SELECT 'MFMBruceSchwarzenegger' UNION ALL

    SELECT 'MFMArnoldDamon' UNION ALL

    SELECT 'MFMLeonardoPitt' UNION ALL

    SELECT 'MFMBruceWahlberg'

    )

    SELECT REPLACE([Business Unit], 'MFM','') FROM MySampleData

  • With MySampleData([Business Unit],[Description 01])

    AS

    (

    SELECT 'MFMGOOGLE','ertCOST POOLert' UNION ALL

    SELECT 'MFMArnoldDeCaprio','COST ert' UNION ALL

    SELECT 'MFMBruceSchwarzenegger','ewtrCOSTwerwer POOL' UNION ALL

    SELECT 'MFMArnoldDamon','COSTerwer ' UNION ALL

    SELECT 'MFMLeonardoPitt','sdfsf' UNION ALL

    SELECT 'MFMBruceWahlberg','fffffCOST_POOL'

    )

    SELECT

    CASE WHEN [Description 01]like '%COST POOL%' AND [Business Unit] like 'MFM%' THEN REPLACE([Business Unit], 'MFM','') END

    FROM MySampleData where CASE WHEN [Description 01]like '%COST POOL%' AND [Business Unit] like 'MFM%' THEN REPLACE([Business Unit], 'MFM','') END

    is not null

  • KoldCoffee (6/2/2013)


    so this is working

    To be sure, you could try something like:

    SUBSTRING(TRIM([Business Unit]), 1, 3) == "MFM" ? SUBSTRING(TRIM([Business Unit]), 4, 50): [Business Unit]

    --snip

    I've tried this but it doesn't parse:

    FINDSTRING([Description 01], "COST POOL", 1))==1?:(SUBSTRING(TRIM([Business Unit]), 1, 3) == "MFM" ? SUBSTRING(TRIM([Business Unit]), 4, 50): [Business Unit])

    I plan to put it into the Conditional Split transformation. Can you help me with the Expression?

    Check this link for examples of the syntax for nested conditional expressions in SSIS. Your syntax is not complete.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • According to that site this should work (proper operands and use of parentheses):

    FINDSTRING(TRIM([Description 01]), "COST POOL", 1))==1?(SUBSTRING(TRIM([Business Unit]), 1, 3) == "MFM" )?(SUBSTRING(TRIM([Business Unit]), 4, 50):[Business Unit])))

    SSIS rejects. Can you see the trouble?

  • Well, I counted only one colon (:) even though you have two conditions. Everything needs to balance.

    No nesting:

    expression1?true1:false1

    Insert one level of nesting:

    expression1?(expression2?true2:false2):false1

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply