String functions not helping

  • Hi

    I'm using a cursor to read values into a char variable, then edit it and place it back into the table. However, if the string is something like Earl's Diner, it doesn't re-insert the row. The offending character needs to be changed from ' to '', but I cannot seem to achieve this with the replace function. Is there a way for me to search through the string and replace ' with '' before trying to add it to the table?

    Thanks

    B

     

     

     

  • It would be helpful, if you can provide the code, along with sample data and desired output.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • try this

    select replace('tester''s', '''','''''')

    >tester''s

    >(1 row(s) affected)

    you need to double (escape) the single quotes so SQL Server can parse them

    cheers

    dbgeezer

  • have you tried replacing using ascii ?! 34 for 39 or some such ?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • Hi

    Thank you very much for your help. I have added the replace function in the select that forms part of my cursor

    declare cr_test cursor for

    select replace(location,char(39),char(34)) from test_table

    open cr_test

    fetch next from cr_test into @location

    while @@fetch_status = 0

    begin

    print @location

    fetch next from cr_test into @location

    end

    close cr_test

    deallocate cr_test

     

    and it works PERFECTLY with my dynamic sql statements! 🙂

    Thanks again!

    Brenchia

     

  • Hi

    Thank you very much for your help. I have added the replace function in the select that forms part of my cursor

    declare cr_test cursor for

    select replace(location,char(39),char(34)) from test_table

    open cr_test

    fetch next from cr_test into @location

    while @@fetch_status = 0

    begin

    print @location

    fetch next from cr_test into @location

    end

    close cr_test

    deallocate cr_test

     

    and it works PERFECTLY with my dynamic sql statements! 🙂

    Thanks again!

    Brenchia

     

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

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