help writing SUBSTRING() expression please

Question

Post reply

help writing SUBSTRING() expression please

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

KoldCoffee SSCoach Points: 18971 More actions · Answer 1

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 2

What do you mean by 'wrong'? Looks OK to me. Does it not parse?

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

KoldCoffee SSCoach Points: 18971 More actions · Answer 3

KoldCoffee

SSCoach

Points: 18971

June 3, 2013 at 11:37 am

#1620671

it does not parse.:-P

Phil Parkin SSC Guru Points: 246965 More actions · Answer 4

KoldCoffee (6/3/2013)
it does not parse.:-P

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

--Edit: added screen shot

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

KoldCoffee SSCoach Points: 18971 More actions · Answer 5

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?

KoldCoffee SSCoach Points: 18971 More actions · Answer 6

Can anyone take a stab at correcting this expression so that it parses? Please?

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

There is no good website for learning how to write an expression where there are two if statements. If the issue here is 'not to spoon feed' and you know the answer then go ahead and give me some tips. A straight answer would be nice too.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Been watching long enough and I have to ask, if it does not parse what (if anything) is the error message you are getting?

KoldCoffee SSCoach Points: 18971 More actions · Answer 8

The field remains red. The field goes black if it is parsing. Well folks, it appears that, none of the regulars on this forum know how to write SSIS derived column expressions.

Thanks, I'll go hunting elsewhere.

Phil Parkin SSC Guru Points: 246965 More actions · Answer 9

KoldCoffee (6/5/2013)
The field remains red. The field goes black if it is parsing. Well folks, it appears that, none of the regulars on this forum know how to write SSIS derived column expressions.
Thanks, I'll go hunting elsewhere.

I wrote out the nested syntax for you and you ignored it.

You told me that an expression which did not parse in the evening somehow magically parsed perfectly the next morning ... but only after I'd set up a test to prove it.

And now you are having a dig at the forum 'regulars' for not helping you. If we were paid, that might be valid, but we operate voluntarily and it is therefore quite offensive.

Good luck elsewhere, and don't hurry back.

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

KoldCoffee SSCoach Points: 18971 More actions · Answer 10

I just figured it out all by myself:

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

That solution was to concatenate. Duh.

Folks here have changed. I used to get a lot of help and attributed my advancement to it. But, alas...it has fallen into the hands of the few.