Error when using replace

  • Hello comunity

    i need to replace the character ' that is contained on my table which the column name is relatorio and type TEXT.

    My select is :

    select replace(convert(text,relatorio),'''','') as relatorio from mh

    Error message :

    Msg 8116, Level 16, State 1, Line 1

    Argument data type text is invalid for argument 1 of replace function.

    Also , if i use :

    select replace(convert(varchar(MAX),relatorio),'''','') as relatorio from mh

    the character ' don´t change to empty.

    Best regards

    Luis Santos

  • If you are truly on sql server 2008, I recommend you look into altering the data type to varchar or nvarchar(max) instead of text. Text data type is going to be deprecated and has been replaced.

    The replace function does not work on Text data types.

    You need to use updatetext. You can read an article about it here: http://www.sqlteam.com/article/search-and-replace-in-a-text-column

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • you are probably trying to substitute a "similar" character. Please verify what is the correct ASCII code or UNICODE for that character.

    By the way your query works!

    declare @t1 table (id int identity(1,1), relatorio text)

    insert into @t1 (relatorio) values('the character [''] will be eliminated')

    select replace(convert(varchar(MAX),relatorio),'''','') as relatorio from @t1


    * Noel

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

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