Need to convert SQL scalar-value function with PATINDEX to VB equivalent

Question

Long time reader, first time poster. Sorry in advance for the wall of text.

Short version: I need to know how to use VB's Instr (or some other VB function) to search a string and return the index of the first occurance of any one of three symbols in that string. Any one of the three symbols may appear any number of times, in any order, in the string. In Tsql, I would search the string using PATINDEX

WHILE PATINDEX('%[#$@]%',@MyString) >0

with #,$, and @ being the individual symbols I need to look for. Note: these are substitutions - the actual symbols used "IRL" are causing problems in my stackOverflow post.

Long Version: I wrote a Tsql Function that accepts a date (a warranty start date), a speical string with symbols and numbers from an application, and a few other necessary inputs, and then loops through the "special string" and performs X number of DATEADD operations, eventually returning a warranty end date. The problem is I need this date for reporting off a DB Warehouse and calling the function for every row like this is too slow. I'd like to move the whole function into a Script Task in the SSIS package that loads the data in the first place, so the calculation can be done in memory and only needs to be done once. Here is the TSQL function for refrence:

            CREATE FUNCTION [dbo].[CalcLDCoverageExp]
            (   
                 @LDCoverage int
                ,@LDCoveragePeriod varchar(max)
                ,@GracePeriod varchar(max)
                ,@dt datetime
                ,@WarrEndt datetime
                )
            RETURNS datetime
            AS
            BEGIN
                Declare @code varchar(max), @symbol varchar(1),@val int
                IF @LDCoverage=1 
                --There is LD coverage
                    BEGIN   
                    IF LEN(rtrim(@LDCoveragePeriod))>0 
                    --There is a Specific LDCoverage period  on the Warrenty Agreement
                        BEGIN
                            SET @code=@LDCoveragePeriod+@GracePeriod    
                            WHILE PATINDEX('%[#$@]%',@code) >0 
                            BEGIN
                                SET @symbol=substring(@code,PATINDEX('%[#$@]%',@code),1)
                                SET @val= Left(@code,PATINDEX('%[#$@]%',@code)-1)
                                SET @dt = (Case @symbol When '#' Then DATEADD(YYYY,@val,@dt)
                                                When '$' Then DATEADD(M,@val,@dt)
                                                When '@' Then DATEADD(D,@val,@dt)
                                                END)
                                SET @code = RIGHT(@code,Len(@code)-PATINDEX('%[#$@]%', @code))
                                --STUFF(@code, PATINDEX('%[#$@]%', @code), 1, '') 
                            END
                        END
                    ELSE
                    --There is not a specific LDCoverage period on the Warrenty Agreement; LD=WarrentyEnd+1Day
                    SET @dt=Dateadd(d,1,@WarrEndt)
                    --END IF LEN(rtrim(@LDCoveragePeriod))>0
                    END
                ELSE
                --No LD Coverage
                SET @dt=NULL
                --END IF @LDCoverage=1 
            RETURN @dt
            END

I have a passing knowledge of VB.net and I'm sure with a little effort and a lot of searching stackoverflow I can convert the rest of the TSQL function to VB equlivalent. But I'm stuck on how to rewrite that PATINDEX statement.

score 0 · Accepted Answer · answered Oct 14 '13 at 21:04

0

VB does not have a standard function for getting the index of "the first occurance of any one of three symbols in that string". You will have to create something that will run the instr() (or use the new .Net version String.IndexOf) 3 times to get the lowest number. Shouldn't be too hard to do.

answered Oct 14 '13 at 21:04

Steve

5,585
2
18
32

Not to say you couldn't do something with RegEx (or other tools), just VB does not have a build-in function. – Steve Oct 14 '13 at 21:05
Would it be more efficient over a very large number of rows to use RegEx rather than run three Instr() and compare in this case? – Voysinmyhead Oct 15 '13 at 12:43
The regex should be faster but you can create both and test them to see which is faster. BTW, for RegEx, I like to use this site [regexlib](http://regexlib.com/). I dont know RegEx well but this site has LOTS of examples posted by users. – Steve Oct 15 '13 at 13:57

Need to convert SQL scalar-value function with PATINDEX to VB equivalent

1 Answers1