SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help writing SUBSTRING() expression please


help writing SUBSTRING() expression please

Author
Message
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4227 Visits: 1905
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()?
Dave23
Dave23
SSC Eights!
SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)

Group: General Forum Members
Points: 969 Visits: 1806
The substring syntax that you are using looks correct to me. What result are you actually getting instead of the expected one?
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73143 Visits: 40960
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!
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4227 Visits: 1905
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
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4227 Visits: 1905
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?
Dave23
Dave23
SSC Eights!
SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)

Group: General Forum Members
Points: 969 Visits: 1806
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]
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4227 Visits: 1905
TRIM did the trick.

thank you very much!!!!!
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4227 Visits: 1905
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.
Dave23
Dave23
SSC Eights!
SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)

Group: General Forum Members
Points: 969 Visits: 1806
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.
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4227 Visits: 1905
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?SadSUBSTRING(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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search