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 Monday, June 3, 2013 2:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 25, 2013 11:06 PM
Points: 202, Visits: 62
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
Post #1459118
Posted Monday, June 3, 2013 2:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 25, 2013 11:06 PM
Points: 202, Visits: 62
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
Post #1459121
Posted Monday, June 3, 2013 6:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 5,162, Visits: 12,010
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1459230
Posted Monday, June 3, 2013 9:06 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 680, Visits: 1,597
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?
Post #1459344
Posted Monday, June 3, 2013 9:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 5,162, Visits: 12,010
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



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1459347
Posted Monday, June 3, 2013 9:53 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 680, Visits: 1,597
let me step back to one expression and go from there.


FINDSTRING([Description 01],"COST POOL", 1)==1? "Yes": "No"

This is the equivalent of
expression1?(expression2?true2:false2):false1

what is wrong with that????
Post #1459366
Posted Monday, June 3, 2013 10:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 5,162, Visits: 12,010
What do you mean by 'wrong'? Looks OK to me. Does it not parse?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1459386
Posted Monday, June 3, 2013 11:37 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 680, Visits: 1,597
it does not parse.
Post #1459410
Posted Tuesday, June 4, 2013 1:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 5,162, Visits: 12,010
KoldCoffee (6/3/2013)
it does not parse.


I just created a text file to check this. It parses for me.

--Edit: added screen shot



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.


  Post Attachments 
Screen shot.jpg (3 views, 13.29 KB)
Post #1459575
Posted Tuesday, June 4, 2013 7:22 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 680, Visits: 1,597
This morning FINDSTRING([Description 01],"COST POOL", 1)==1? "Yes": "No" parsed. So back to the original problem.

I need one level of nesting to handle second condition. Following SQLChik's tutorial...
Field == "TrueCondition1" ? “True” : ( Field == "TrueCondition2" ? “True” : ( Field == "TrueCondition3 " ? “True” : ( Field == "TrueCondition4" ? “True” : “False" ) ) )


I note there are many TRUEs and one FALSE if all hopes are dashed, so I do this:


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


Translated: see if the string "Cost Pool" exists in [Description 01] column, if it does, see if first 3 letters of [Business Unit] are "MFM", if yes, retain the letters after MFM. But, if "Cost Pool" doesn't exist, put NULL in column.

This expression doesn't parse. Is it because I am missing parentheses?
Post #1459757
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse