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-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 25, 2013 11:06 PM
Points: 196, 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-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 25, 2013 11:06 PM
Points: 196, 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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:02 AM
Points: 4,983, Visits: 11,678
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
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:33 PM
Points: 610, Visits: 1,417
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?


it helps to talk it out
Post #1459344
Posted Monday, June 3, 2013 9:14 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:02 AM
Points: 4,983, Visits: 11,678
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
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:33 PM
Points: 610, Visits: 1,417
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????


it helps to talk it out
Post #1459366
Posted Monday, June 3, 2013 10:40 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:02 AM
Points: 4,983, Visits: 11,678
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
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:33 PM
Points: 610, Visits: 1,417
it does not parse.

it helps to talk it out
Post #1459410
Posted Tuesday, June 4, 2013 1:22 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:02 AM
Points: 4,983, Visits: 11,678
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
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:33 PM
Points: 610, Visits: 1,417
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?


it helps to talk it out
Post #1459757
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse