Solved

How do I replace strings in Text datatype without truncating to 8000 chars

Posted on 2011-09-12
11
469 Views
Last Modified: 2012-05-12
Hi,

I have a Text Datatype column and I need to replace some of the strings within them. I did try to use the REPLACE function like so:

UPDATE <Table> set textcolumn=
REPLACE(SUBSTRING(textcolumn,1,DATALENGTH(textcolumn)),'findtext','replacetext')
WHERE <Condition>


However I have now discovered that the SUBSTRING truncates to 8000 chars.

I am using SQL Server 2005.

How can I do a replace in this Text column without truncation.

Thanks,

Sam
0
Comment
Question by:SamJolly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 5

Accepted Solution

by:
DerZauberer earned 200 total points
ID: 36523155
0
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36523190
Hi Sam


If you do an explicit conversion to varchar(max) first this should allow you to use replace across the while value.

so something like this (untested):

UPDATE <Table> set textcolumn=
REPLACE(SUBSTRING(cast(textcolumn as varchar(max)),1,DATALENGTH(textcolumn)),'findtext','replacetext')
WHERE <Condition>

does that do what you need?


Thanks

Dave
0
 

Author Comment

by:SamJolly
ID: 36523224
Thanks for the quick replies....

As long as the full contents of the Text DataType column can be guaranteed not to be truncated then that is what I need.

Sam

0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
LVL 5

Assisted Solution

by:DavidMorrison
DavidMorrison earned 200 total points
ID: 36523240
Hi Sam, what I'd do to test would be run a select, putting a LEN around the converted column to ensure it's maintaining all the full string



Thanks

Dave
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 36526853
>>However I have now discovered that the SUBSTRING truncates to 8000 chars. <<
Actually it does not.  SUBSTRING supports text data types as can be easily verified.  However, the correct solution as suggested previously is to convert all your code from the deprecated text data type to varchar(MAX)
0
 

Author Comment

by:SamJolly
ID: 36526879
Hi acperkins,

Yes I have now picked up on this. My comment would have been correct for SQL2000/SqlServer7, but not for SQL Server 2005 and beyond. I have found the MAX solution works great.

Thanks,

Sam
0
 

Author Comment

by:SamJolly
ID: 36526883
I am splitting the points as 2 MAX solutions and one verification !

Thanks,

Sam
0
 

Author Closing Comment

by:SamJolly
ID: 36526887
thks. Fab help.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36526901
>>My comment would have been correct for SQL2000/SqlServer7<<
Actually no.  SUBSTRING on text data types beyond 8000 characters was also supported with SQL Server 2000.
0
 

Author Comment

by:SamJolly
ID: 36526912
Ok... I have been reading duff info....

Thanks for clarifying.

Sam
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36527124
Try this in SQL Server 2000:
-- First create a temporary table and fill some data in a text column:
CREATE TABLE #MyTable (TextCol text)

DECLARE @ColPtr binary(16),
	@Value varchar(6000)

INSERT  #MyTable
        (TextCol)
VALUES  (REPLICATE('A', 6000))

SELECT  @ColPtr = TEXTPTR(TextCol)
FROM    #MyTable

SET @Value = REPLICATE('B', 6000)

UPDATETEXT #MyTable.TextCol @ColPtr NULL 0 @Value

SET @Value = REPLICATE('C', 6000)

UPDATETEXT #MyTable.TextCol @ColPtr NULL 0 @Value


-- Next output the results from different sections using SUBSTRING
SELECT  SUBSTRING(TextCol, 10000, 40),
        SUBSTRING(TextCol, 15000, 40)
FROM    #MyTable

-- Finally drop the table
DROP TABLE #MyTable



Output:
---------------------------------------- ----------------------------------------
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC

Open in new window

0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question