Conditional Split - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Conditional Split

Author

Topic

mateo958
Starting Member

14 Posts

Posted - 2009-10-12 : 05:47:09

Hi!

I want to create a conditional Split and in one of my conditions I want to make out a will if the last character is a letter.

quote:

[DUNS Number] == "*?"

example : 112233434355A

A solution?

mateo958
Starting Member

14 Posts

Posted - 2009-10-13 : 03:27:12

Up ;)

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-13 : 05:58:25

Here's one option for the condition:


CODEPOINT(REVERSE(UPPER(ColumnName))) >= 65 && CODEPOINT(REVERSE(UPPER(ColumnName))) <= 90

mateo958
Starting Member

14 Posts

Posted - 2009-10-13 : 10:21:15

Hi Yellowbug,

i'm sorry but i don't understand your solution (i'm french ^^)

I want to test the column [DUNS Number] if the value is a number and finish by a letter.

example :
112233434355A
or
154364756756B
and not
1232434534535

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-13 : 11:02:42

The above will only check that the last character is a letter between A to Z.
If you use it as the condition in the conditional split, you'll see that the strings that don't end in a letter are filtered out.
The solution works like this:
- convert to UPPER case
- REVERSE the string: to get the last character only
- CODEPOINT: gives the ASCII value of the character. Here's a link to an ascii table: http://www.asciitable.com/

SSIS does not have the IS_NUMERIC function, as in T-SQL. So, will have to look for a way to check that the rest of the string is numeric.

Sorry, but my French is worse than your English. I hope this is clear. Let us know if not.

mateo958
Starting Member

14 Posts

Posted - 2009-10-14 : 03:47:12

Thank you very much YellowBug, your explanation is very clear and I understand now.

But now i have a problem ;)

It's my fault because I did not quite comprehensive, I have three conditions.
- Those who end up a letter (your solution is good)
- Those not start MD (where I'm wondering how to do it because for me it's the same solution as above but without REVERSE neither will work.
- Those who do not fit in the two conditions above

Sorry for my bad english and thank you for your patience and your help

mateo958
Starting Member

14 Posts

Posted - 2009-10-14 : 04:34:09

Hey Yellow, I show you the solution that I think should work because I run my test package and then I changed the values below so if I can get a confirmation before I raise my experiment with new values

- CODEPOINT(REVERSE(UPPER([DUNS Number]))) >= 65 && CODEPOINT(REVERSE(UPPER([DUNS Number]))) <= 90
- CODEPOINT(UPPER([DUNS Number])) == 77
- CODEPOINT(REVERSE(UPPER([DUNS Number]))) <= 64 || CODEPOINT(REVERSE(UPPER([DUNS Number]))) >= 91) && CODEPOINT(UPPER([DUNS Number])) != 77

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-14 : 07:48:21

For the second case (not start MD), you can use:
SUBSTRING(UPPER([DUNS Number]),1,2) != "MD"

And maybe use the Conditional Split Default Output for all other records.

Hope this helps.

mateo958
Starting Member

14 Posts

Posted - 2009-10-14 : 08:29:42

I'm sorry for the second cas is strat MD ^^

I replace != by ==

Thanks, i test tomorow because i can't today

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-14 : 08:34:42

Ok. Thanks for the update.

mateo958
Starting Member

14 Posts

Posted - 2009-10-16 : 08:51:07

Hey Yellow Bug,
thanks for your help, it's ok for case 1 and 2 but for case 3 i use error output but nothing happens in this case :(

I tried to put
(CODEPOINT(REVERSE(UPPER([DUNS Number]))) <= 65 && CODEPOINT(REVERSE(UPPER([DUNS Number]))) >= 90) && SUBSTRING(UPPER([DUNS Number]),1,2) != "MD"

in case 3, but I did nothing out yet

mateo958
Starting Member

14 Posts

Posted - 2009-10-16 : 09:02:05

Yessss,

CODEPOINT([DUNS Number]) >= 48 && CODEPOINT([DUNS Number]) <= 57
in the case 3 and it's good,

thanks you very much ;)

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-16 : 09:02:48

Try using the Conditional Split Default Output for this last case - delete the 3rd condition and drag another green arrow out of the Conditional Split task.

Does this work?

mateo958
Starting Member

14 Posts

Posted - 2009-10-16 : 09:12:14

It's ok with the post before you ;)

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-16 : 09:32:43

Yes, I see. I was typing when you posted success above:-)
Glad it's working.

"CODEPOINT([DUNS Number]) >= 48 && CODEPOINT([DUNS Number]) <= 57 "
only checks for first character is between zero and nine, right?

What do you do with any other data? Or is this not a concern?

112233434355A - Condition 1
MD22334343552 - Condition 2
1122334343559 - Condition 3
+122334343552 - Ignore ?
-122334343552 - Ignore ?
.122334343551 - Ignore ?

mateo958
Starting Member

14 Posts

Posted - 2009-10-16 : 10:01:20

112233434355A - Condition 1 ---> OK
MD22334343552 - Condition 2 ---> OK
1122334343559 - Condition 3 ---> OK
+122334343552 - Ignore ? ---> In this case, I have no input;)
-122334343552 - Ignore ? ---> In this case, I have no input;)
.122334343551 - Ignore ? ---> In this case, I have no input;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources