February 3, 2012 at 12:35 am
Hi,
I have created one table. it contains id, textdata columns, datatype is text only. It has more than 10000 characters stored for a single textdata column.
I need to remove the specific line from the textdata column. So i used the below function.
select replace(textdata,'
-----sample data are available','')
if i used the above query. it throws an error. like
Argument datatype text is invalid for argument 1 of replace function.
Could you please help us to resolve this issue..
Thanks in Advance
Regards
Balaji G
February 8, 2012 at 4:06 am
Cast your column as varchar(MAX) first
select REPLACE (cast(col as varchar(max)),'value','replace_value')
***The first step is always the hardest *******
February 8, 2012 at 4:38 am
SGT_squeeqal (2/8/2012)
Cast your column as varchar(MAX) first
select REPLACE (cast(col as varchar(max)),'value','replace_value')
That won't work in SQL Server 2000, which is this forum.
Untested, but could you split it into chunks, run your replace then use WRITETEXT to overwrite the text?
e.g. This would split the text up for you to run a replace.
SELECT REPLACE(chunks, 'a', 'b') AS chunks, n
FROM (
SELECT CAST(SUBSTRING(col, CASE
WHEN n = 1
THEN 1
ELSE 4000 * (n - 1)
END, CASE
WHEN n <= DATALENGTH(col) / 4000
THEN 4000
ELSE DATALENGTH(col) - ((DATALENGTH(col) / 4000) * 4000)
END) AS VARCHAR(4000)) AS chunks, n
FROM #test
INNER JOIN (SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
) num ON n <= (DATALENGTH(col) / 4000) + 1
) cnk
February 8, 2012 at 6:26 am
change the varchar(MAX) then to VARCHAR(8000) this chould work, they did say there was about 1000 charachters
***The first step is always the hardest *******
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply