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.

Answer 1

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.