Stored Procedure

  • I have to create a store procudure....below is description..

    I have a IDcode ,,,,,and length is 6.

    like

    00000}

    00000A

    00000B

    12323C

    .........

    .........

    Now I want to change last digit means I have to put 0 instead of }

    put 1 instead of A

    for each row...

    below is description ,,,I want to change last digit,

    { 0

    A 1

    B 2

    C 3

    D 4

    E 5

    F 6

    G 7

    H 8

    I 9

    and

    } 0

    J -1

    K -2

    L -3

    M -4

    N -5

    O -6

    P -7

    Q -8

    R -9

    How can i do with stored procedure and I also want to do with SSIS package ...like derived column??

    Please Help me,

    Thanks

  • Use the REPLACE fimction

    REPLACE(find, replace, string)

    find

    Specifies the string that contains the substring to replace all instances of with another.

    replace

    Specifies the substring to locate.

    string

    Specifies the substring with which to replace the located substring.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • REPLACE will work, of course, but direction is needed as to what to REPLACE *with*.

    I'm no expert, but I played around a bit with your requirements, and I think at least part of the answer is thus:

    Declare @IDCode char(6)

    set @IDCode = '12323C'

    select ((ascii(right(@IDCode,1))%65)+1)

    This works well for 'A' through 'I', so some test would have to be performed for higher letters going into more digits, and, of course, this doesn't account for the case of '}'

    Hope this helps a little bit, anyway.

Viewing 3 posts - 1 through 2 (of 2 total)

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