Solved

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

Posted on 2011-09-12
11
446 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
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 5

Accepted Solution

by:
DerZauberer earned 200 total points
Comment Utility
0
 
LVL 5

Expert Comment

by:DavidMorrison
Comment Utility
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
Comment Utility
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
 
LVL 5

Assisted Solution

by:DavidMorrison
DavidMorrison earned 200 total points
Comment Utility
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
Comment Utility
>>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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:SamJolly
Comment Utility
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
Comment Utility
I am splitting the points as 2 MAX solutions and one verification !

Thanks,

Sam
0
 

Author Closing Comment

by:SamJolly
Comment Utility
thks. Fab help.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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
Comment Utility
Ok... I have been reading duff info....

Thanks for clarifying.

Sam
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now