need to convert oracle function into c# code

  • create or replace

    FUNCTION "GETSEPARATEDSTRING"

    ( pString IN VARCHAR2

    ,pSeparator IN VARCHAR2

    ,pReturnNumber IN PLS_INTEGER

    )

    RETURN VARCHAR2

    IS

    l_SearchString_FinPos PLS_INTEGER :=0;

    l_SearchString_StartPos PLS_INTEGER :=0;

    l_SearchString_Length PLS_INTEGER :=0;

    l_SearchString_CurrentPos PLS_INTEGER :=0;

    l_Return VARCHAR2(4000);

    BEGIN

    -- expecting values as String Seperator String Seperator

    -- so if pReturnNumber = 2 then where are

    -- looking for seperators 2 and 1. If there is no seperator

    -- at the end of the string it is added before comparison,

    -- Will return a null if:

    -- The length of pString is > 4000

    -- The pSeparator has not been specified

    -- The pReturnNumber IS <= 0

    -- The pReturnNumber IS greater than the number of pSeparator + 1 and therefore we can't pick up a string

    -- There was an empty string at the position requested

    -- Strings are returned without pSeparator

    IF LENGTH( pString || pSeparator ) <= 4000

    AND pSeparator IS NOT NULL

    AND pReturnNumber > 0

    THEN

    l_SearchString_FinPos := pReturnNumber;

    l_SearchString_StartPos := pReturnNumber - 1;

    -- Concat a seperator at the end of the string so at least we

    -- know there is one

    IF INSTR( pString, pSeparator, -1, 1) != ( LENGTH( RTRIM( pString )) - LENGTH( pSeparator ) + 1 )

    THEN

    -- There isn't one at the end so add it

    l_Return := pString || pSeparator;

    --DBMS_OUTPUT.PUT_LINE('Did not find seperator - ADDING');

    ELSE

    l_Return := pString;

    --DBMS_OUTPUT.PUT_LINE('FOUND seperator');

    END IF;

    -- Set the start position of where we will check to the

    -- the last position we found a pSeparator value.

    l_SearchString_CurrentPos := l_SearchString_FinPos;

    -- Search for the next pSeparator position

    l_SearchString_FinPos := INSTR( l_Return, pSeparator, 1, l_SearchString_CurrentPos );

    IF l_SearchString_FinPos != 0

    THEN

    IF l_SearchString_StartPos != 0

    THEN

    l_SearchString_CurrentPos := l_SearchString_StartPos;

    l_SearchString_StartPos := INSTR( l_Return, pSeparator, 1, l_SearchString_CurrentPos ) + 1;

    ELSE

    -- If we are looking for the first value then StartPos will = 0

    -- and cause INSTR to fail

    l_SearchString_CurrentPos := 1;

    END IF;

    l_SearchString_Length := l_SearchString_FinPos - l_SearchString_StartPos;

    l_Return := RTRIM( SUBSTR( l_Return, l_SearchString_StartPos, l_SearchString_Length ), pSeparator );

    ELSE

    l_Return := NULL;

    END IF;

    END IF;

    RETURN l_Return;

    EXCEPTION

    WHEN OTHERS

    THEN

    DBMS_OUTPUT.PUT_LINE( 'FUNCTION GetSeperatedString Captured Error: ' || SQLERRM );

    RETURN NULL;

    END;

  • IMHO This is pretty ugly code. I cannot write C#, but all it is doing is selecting element N from a delimited list. You could replace it with something like this http://www.sqlservercentral.com/scripts/ETL/68512/ which has almost the same parameters. No need for CLR. HTH

  • Take a look at the splitter function in my signature from Jeff Moden. The DelimitedSplit8Kfunction can do exactly that with no looping.

    For example if you want element 3 from 1,2,3,4,5 it is this simple

    select * from DelimitedSplit8K('1,2,3,4,5', ',')

    where Item = 3

    Easy and fast!!!

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • in C#.Ner or VB.Net, it's built into the Striigs Class

    Dim MyArray() As String = AnyStringObject.Split(",")

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Nice signature, Lowell. 😀

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply