May 31, 2013 at 6:08 am
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()?
May 31, 2013 at 6:15 am
The substring syntax that you are using looks correct to me. What result are you actually getting instead of the expected one?
May 31, 2013 at 6:23 am
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
May 31, 2013 at 6:25 am
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:
May 31, 2013 at 6:32 am
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?
May 31, 2013 at 6:41 am
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]
May 31, 2013 at 6:58 am
TRIM did the trick.
thank you very much!!!!!
May 31, 2013 at 7:02 am
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.
May 31, 2013 at 7:06 am
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.
June 2, 2013 at 10:44 am
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?
June 3, 2013 at 2:46 am
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
June 3, 2013 at 2:58 am
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
June 3, 2013 at 6:38 am
KoldCoffee (6/2/2013)
so this is workingTo 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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 3, 2013 at 9:06 am
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?
June 3, 2013 at 9:14 am
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply