Conditional split Expression help

Question

Conditional split Expression help

r_corp

SSC-Addicted

Points: 457
More actions
July 23, 2012 at 1:11 pm

#261513

I have 4 columns coming in my source file.
Lightwave 1920 West 2012-07-20
Princeton9 250 East 2012-07-18
lewisville 500 Mid-west 2012-06-25
canon dr8 650 West 2012-07-05
If my first column value has a number in it, I want those rows to be errored out and not send it to a destination file.
For example I want to error out second row because the value princeton9 has a number(9) in it.
So my destination file have rows 1 and 3 and my error output have 2 and 4.
I understand that I have to use conditional split but I couldnt figure out the expression to use.
Thanks for your time and any help would be appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I think you'll need to use a script component and .NET RegEx functionalisty to get this to work.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

r_corp SSC-Addicted Points: 457 More actions · Answer 2

Jack Corbett (7/24/2012)
I think you'll need to use a script component and .NET RegEx functionalisty to get this to work.

Do you know of any samples which I can leverage? I am not a programmer so it will be hard to get this done.

Any help would be appreciated. Thanks.

Phil Parkin SSC Guru Points: 247158 More actions · Answer 3

Are SQL Server tables involved at any point in this process? Or is this a file-to-file job?

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

I agree with Jack.

But I can envisage a revolting non-programming option.

Create a derived column which is a massive nested replace - to replace any of 0, 1, 2, ..., 9 with an empty string.

Then compare the length of this derived column with the length of the original string - if different, error.

Now I feel dirty! Regex is the elegant way

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 5

excuse double post i replied to the other post before seeing the link

you could achieve your reslutls by using patindex

PATINDEX('%[0-9]%',col1)=0

if there is a value between 0 and 9 in col1 then these rows will be discounted from the result set

***The first step is always the hardest *******

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 6

SGT_squeequal (7/24/2012)
excuse double post i replied to the other post before seeing the link
you could achieve your reslutls by using patindex
PATINDEX('%[0-9]%',col1)=0
if there is a value between 0 and 9 in col1 then these rows will be discounted from the result set

+1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 247158 More actions · Answer 7

Jeff Moden (7/24/2012)
SGT_squeequal (7/24/2012)
excuse double post i replied to the other post before seeing the link
you could achieve your reslutls by using patindex
PATINDEX('%[0-9]%',col1)=0
if there is a value between 0 and 9 in col1 then these rows will be discounted from the result set
+1

There is no patindex function in SSIS.

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 8

i had not noticed this was for SSIS :w00t:

***The first step is always the hardest *******

Conditional split Expression help

Cookies on SQLServerCentral