October 24, 2011 at 3:13 pm
how can i find the position of a numeric value by using FINDSTRING function in ssis
see example below
code
-------------
aaaaa 1aaaaaa
bbbbb2bbbbbbb
cccc3cccccccc
I need output as a position of a numerica value, in this case output is
output
---------
7
6
5
October 24, 2011 at 4:55 pm
I'm not 100% sure about SSIS but, from what I've read, you should be able to use "%[0-9]%" as the "search string" in FINDSTRING. Of course, you'd use 1 as the "occurance" parameter.
Hopefully, someone who's actually done such a thing will come along and either verify or correct, either of which would be appreciated.
--Jeff Moden
October 24, 2011 at 5:01 pm
Jeff Moden (10/24/2011)
I'm not 100% sure about SSIS but, from what I've read, you should be able to use "%[0-9]%" as the "search string" in FINDSTRING. Of course, you'd use 1 as the "occurance" parameter.Hopefully, someone who's actually done such a thing will come along and either verify or correct, either of which would be appreciated.
You can't. It's not available. FINDSTRING does not support wildcards or ranges.
Pass it to a synchronous transformation script object and handle it there in the data flow.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 25, 2011 at 7:03 am
A bit of an uglier solution, but if you want to avoid using a script component and writing a .NET script to do it, you could do something like:
FINDSTRING([string], "0", 1) > 0 ? FINDSTRING([string], "0", 1) : (FINDSTRING([string], "1", 1) > 0 ? FINDSTRING([string], "1", 1) : (FINDSTRING([string], "2", 1) > 0 ? FINDSTRING([string], "2", 1) : (FINDSTRING([string], "3", 1) > 0 ? FINDSTRING([string], "3", 1) : (FINDSTRING([string], "4", 1) > 0 ? FINDSTRING([string], "4", 1) : (FINDSTRING([string], "5", 1) > 0 ? FINDSTRING([string], "5", 1) : (FINDSTRING([string], "6", 1) > 0 ? FINDSTRING([string], "6", 1) : (FINDSTRING([string], "7", 1) > 0 ? FINDSTRING([string], "7", 1) : (FINDSTRING([string], "8", 1) > 0 ? FINDSTRING([string], "8", 1) : FINDSTRING([string], "9", 1)))))))))
October 25, 2011 at 7:07 am
kramaswamy (10/25/2011)
A bit of an uglier solution, ...
Only a bit? 😀
I was thinking along similar lines, but couldn't face writing out all that nesting!
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 25, 2011 at 7:27 am
Wow! Really? Isn't there any function in SSIS that's similar to PATINDEX?
--Jeff Moden
October 25, 2011 at 7:48 am
I don't think so. There are custom components available here[/url], including more than one Regex component which looks promising, though I've never used any of them. Rather than doing that, I'd probably script it, as has already been suggested.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 25, 2011 at 7:54 am
Yeah putting in a script component and just using .NET's regex libraries would be pretty simple
October 25, 2011 at 12:01 pm
Kramaswamy, my biggest concern with the nested choices are if you have something like:
aaaaaaaa93aaaaaa.
3 gets picked up first. You'd have to test for all possibilities and pick the lowest non-zero value. It gets nasty fast when trying to build your own regex.
Synchronous script component in SSIS is something you really want to learn to do, particularly for cases like this. Heck, string manipulation is part of the speed benefit of CLR in the SQL Engine, might as well learn it here, too.
Jeff Moden (10/25/2011)
Wow! Really? Isn't there any function in SSIS that's similar to PATINDEX?
No, not in expressions. A bit weaksauce, I know.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 25, 2011 at 12:41 pm
yeah definitely agreed Craig. Just posted that incase he's looking for something quick.
What's this synchronous script component you speak of? Is that something new in 2008?
October 25, 2011 at 1:09 pm
kramaswamy (10/25/2011)
yeah definitely agreed Craig. Just posted that incase he's looking for something quick.What's this synchronous script component you speak of? Is that something new in 2008?
Nope, it's in '05. It depends on the settings you use to associate the inbound and outbound result sets.
Official docs:
http://msdn.microsoft.com/en-us/library/ms136114.aspx
A discussion of Synchronous vs. Asynchronous:
The key difference is if you set the SynchronousInputID in the Output properties. It changes your code a little though because you don't have to do the code to 'release' rows.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 25, 2011 at 1:15 pm
Ahh okay, that's what you meant. Alright, yeah I've done that before as well. Though interesting to see the performance gains that are mentioned in the thread on Jamie's site.
October 25, 2011 at 4:50 pm
Evil Kraig F (10/25/2011)
Kramaswamy, my biggest concern with the nested choices are if you have something like:aaaaaaaa93aaaaaa.
3 gets picked up first. You'd have to test for all possibilities and pick the lowest non-zero value. It gets nasty fast when trying to build your own regex.
Synchronous script component in SSIS is something you really want to learn to do, particularly for cases like this. Heck, string manipulation is part of the speed benefit of CLR in the SQL Engine, might as well learn it here, too.
Jeff Moden (10/25/2011)
Wow! Really? Isn't there any function in SSIS that's similar to PATINDEX?No, not in expressions. A bit weaksauce, I know.
It IS "weaksauce" indeed! 🙂
As for the string manipulations of CLR, I agree but only in some cases. Several years ago, Matt Miller and I engaged in some friendly competition and I was able to write T-SQL that would sometimes beat REGEX in CLR's. It follows the old rule that generic code is generically good and specific code for specific problems is frequently better.
--Jeff Moden
October 25, 2011 at 6:08 pm
Jeff Moden (10/25/2011)
It IS "weaksauce" indeed! 🙂As for the string manipulations of CLR, I agree but only in some cases. Several years ago, Matt Miller and I engaged in some friendly competition and I was able to write T-SQL that would sometimes beat REGEX in CLR's. It follows the old rule that generic code is generically good and specific code for specific problems is frequently better.
Considering how often I write CLRs... (never, btw...) I find it hard to disagree with you. 😉 However, had CLRs been available back in 7.0/2k, my guess is Patindex, contains, etc would not have been robustly developed.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 25, 2011 at 6:31 pm
Heh... then thank goodness CLR's weren't invented back then. 😀
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply