Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

help writing SUBSTRING() expression please Expand / Collapse
Author
Message
Posted Friday, May 31, 2013 6:08 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:45 PM
Points: 519, Visits: 1,209
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()?

Post #1458631
Posted Friday, May 31, 2013 6:15 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 252, Visits: 1,697
The substring syntax that you are using looks correct to me. What result are you actually getting instead of the expected one?
Post #1458634
Posted Friday, May 31, 2013 6:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:39 AM
Points: 12,744, Visits: 31,070
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1458636
Posted Friday, May 31, 2013 6:25 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:45 PM
Points: 519, Visits: 1,209
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).

Post #1458638
Posted Friday, May 31, 2013 6:32 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:45 PM
Points: 519, Visits: 1,209
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?
Post #1458640
Posted Friday, May 31, 2013 6:41 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 252, Visits: 1,697
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]
Post #1458643
Posted Friday, May 31, 2013 6:58 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:45 PM
Points: 519, Visits: 1,209
TRIM did the trick.

thank you very much!!!!!
Post #1458654
Posted Friday, May 31, 2013 7:02 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:45 PM
Points: 519, Visits: 1,209
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.
Post #1458655
Posted Friday, May 31, 2013 7:06 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 252, Visits: 1,697
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.
Post #1458657
Posted Sunday, June 02, 2013 10:44 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:45 PM
Points: 519, Visits: 1,209
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?
Post #1459018
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse