[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 488
  • Last Modified:

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

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
SamJolly
Asked:
SamJolly
  • 5
  • 3
  • 2
  • +1
3 Solutions
 
DavidMorrisonCommented:
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
 
SamJollyAuthor Commented:
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
DavidMorrisonCommented:
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
 
Anthony PerkinsCommented:
>>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
 
SamJollyAuthor Commented:
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
 
SamJollyAuthor Commented:
I am splitting the points as 2 MAX solutions and one verification !

Thanks,

Sam
0
 
SamJollyAuthor Commented:
thks. Fab help.
0
 
Anthony PerkinsCommented:
>>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
 
SamJollyAuthor Commented:
Ok... I have been reading duff info....

Thanks for clarifying.

Sam
0
 
Anthony PerkinsCommented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now