SQL Cursor Logic

  • Hi Team,

    Using below table.

    UserStrg_1Strg_2

    --------------------------------

    MiamiTestTest_1

    MiamiTest_1Test_2

    MiamiTest_2Test_3

    MiamiTest_3Test_4

    MiamiTest_4Test_5

    to replace the strg_1 value with Strg_2 value using below cursor, but the cursor is iterating 5 times and the select query in below cursor is also returning 5 times, and the replace function is not working.

    Declare @text1 varchar(max)

    Declare @test2 varchar(max)

    DECLARE Test_CUR CURSOR FOR SELECT col1, col2

    FROM FNX_LNX WITH (NOLOCK)

    WHERE

    RTRIM(city)='Miami'

    OPEN Test_CUR

    FETCH NEXT FROM Test_CUR INTO @text1, @text2

    WHILE @@FETCH_STATUS = 0

    BEGIN

    select id,name,

    LTRIM(RTRIM(REPLACE(name,@text1,@text2))) New_name

    from UAT

    FETCH NEXT FROM Test_CUR INTO @text1, @text2

    END

    CLOSE Test_CUR

    DEALLOCATE Test_CUR

    Please help to to resolve the logic.

  • Minnu (5/25/2015)


    Hi Team,

    Using below table.

    UserStrg_1Strg_2

    --------------------------------

    MiamiTestTest_1

    MiamiTest_1Test_2

    MiamiTest_2Test_3

    MiamiTest_3Test_4

    MiamiTest_4Test_5

    to replace the strg_1 value with Strg_2 value using below cursor, but the cursor is iterating 5 times and the select query in below cursor is also returning 5 times, and the replace function is not working.

    Declare @text1 varchar(max)

    Declare @test2 varchar(max)

    DECLARE Test_CUR CURSOR FOR SELECT col1, col2

    FROM FNX_LNX WITH (NOLOCK)

    WHERE

    RTRIM(city)='Miami'

    OPEN Test_CUR

    FETCH NEXT FROM Test_CUR INTO @text1, @text2

    WHILE @@FETCH_STATUS = 0

    BEGIN

    select id,name,

    LTRIM(RTRIM(REPLACE(name,@text1,@text2))) New_name

    from UAT

    FETCH NEXT FROM Test_CUR INTO @text1, @text2

    END

    CLOSE Test_CUR

    DEALLOCATE Test_CUR

    Please help to to resolve the logic.

    First, you've been around long enough to know how to post data to get the quickest response. If not, please see the article at the first link in my signature lines below under "Helpful Links". The extra couple of minutes you spend on such a thing will make it much easier for someone to help you and you'll get tested code in return.

    Second, I don't know why you would want to duplicate the data in one column in another column. Please explain why this is necessary because it's a waste of disk and memory space due to the duplication of data.

    Third, you definitely do NOT need a cursor for this. The following code will do it all for you. Of course, I've not tested it because you've not provided the data in the readily consumable format that's mentioned in the article I've referred you to.

    Forth, you don't need REPLACE for this. Not sure it would work to begin with but I can't tell a thing from your code example. I'm going only by your description that you want to replace Strg_1 (the whole string) Strg_2 (the whole string).

    UPDATE dbo.FNX_LNX

    SET Strg_1 = LTRIM(RTRIM(Strg_2))

    WHERE User = 'Miami'

    ;

    I also strongly recommend avoiding the use of WITH(NOLOCK). It can allow duplicate data to occur.

    Since your description appears to be contrary to what it looks like you were doing in the code, please don't hesitate to come back with added clarification to the problem along with some readily consumable test data.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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