May 6, 2010 at 11:16 am
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
May 6, 2010 at 11:26 am
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
May 6, 2010 at 11:38 am
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