Actually, set sTemp='' consumes 13.6% of exec time and if line 86.4% |
I have rewrited using your tips. Thank you all!
1
Did you run some tests and get give us a hint of the performance improvements ?
(19 Jan '12, 07:03)
Thomas Dueme...
Replies hidden
...a fine suggestion, though possibly a bit late...:)
(19 Jan '12, 07:30)
Volker Barth
|
Another approach is to convert the string's characters into a set of row values and use a WHERE clause to eliminate the non-numeric ones, then put the result back together using the LIST aggregate function.
|
I tried Bruce's variant with the LIST aggregate and I was surprised that it is actually faster (with my test data):
I timed the two variants using fetchtst on 11.0.1.2001 using some fabricated strings from systabcol:
The following file was used by fetchtst -ga:
Calling the function 63180 times took 39.2s for the onlyNumber() implementation and 3.2s for the BH_onlyNumber() variant. These results may not hold if your data is distributed in a different way or if you are not calling many times from a single query. |
I missed a couple days so didn't see this. I think the patindex is definitely the way to go. Although I don't like the loop at the end.
This might change based upon whether we expected lots of non numerics and whether non numerics might show up grouped together or not which is what I designed for above. If we're just looking at something like SSN 999-99-9999 then the set iCurPos should just be set iCurPos = iCurPos + iNonNum; Jon |
Similar to Bruce's approach, this solution may prove useful or inspire solutions to other problems: CREATE OR REPLACE FUNCTION onlyNumber(IN val LONG VARCHAR) RETURNS LONG VARCHAR DETERMINISTIC BEGIN DECLARE ret LONG VARCHAR; SELECT LIST( IF isNumeric(SUBSTRING(val,row_num,1)) = 1 THEN SUBSTRING(val,row_num,1) //preserve digit ELSE '' //remove non-digit END IF ,'' ORDER BY row_num) INTO ret FROM sa_rowgenerator(1,LENGTH(val)); //1 "row" for each character RETURN ret; END; |
Are the number characters always together, or is there something between number chars? What kind of input will the procedure get? You might be able to use a regular expression to extract the numbers.