UPDATETEXT [RESOLVED] - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

UPDATETEXT [RESOLVED]

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-01 : 12:48:22

Does anyone know anything about UPDATETEXT?

Right now I have text that has quotes around it. I need to remove those, but it is in a Text field(which it is big enough where it needs to be).
[code]
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(notes)
FROM tblParadox
UPDATETEXT tblParadox.notes @ptrval 1 '"' ''
[code]

Anyone know how to fix that so it will take out the quotes? Thanks!

Brenda

If it weren't for you guys, where would I be?

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-01 : 15:02:28

Break it up in to 8k chunks and use replace

Or do what I suggested before

Brett

8-)

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-01 : 15:08:02

I would use your other idea(thanks by the way), but i figured out how to insert it into 3 columns using the export/import wizard.

How would I break it up in to 8000 bytes?

Brenda

If it weren't for you guys, where would I be?

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-01 : 15:37:28

You have to concat all of the substrings together in 1 shot. You didn't delete the data, you replaced with data starting from 8001 and on.

More likely there was no data in those locations. except for a rare few rows.


UPDATE myTable99 
   SET Col1 = REPLACE(SUBSTRING(Col1,1,8000),'x','a') + REPLACE(SUBSTRING(Col1,8001,8000),'y','a')
 WHERE pkCol = 1 
GO

Brett

8-)

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-01 : 16:05:48

Everything works fine, except when I do this:

update tblparadox
set notes = REPLACE(SUBSTRING(notes,8001,8000),'"','')

When I do that, it deletes everything. Do you know why?

Brenda

If it weren't for you guys, where would I be?

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-01 : 16:42:56

Damn, I did an edit instead of a reply...And I still think this is a bad idea. My guess is you can go up to 32k before you start needing to use READTEXT, UPDATETEXT and WRITETEXT

The original code


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(pkCol int, Col1 text)
GO

DECLARE @x varchar(8000), @y varchar(8000)
SELECT @x = REPLICATE('x',8000), @y = REPLICATE('y',8000)

-- mimic the load
INSERT INTO myTable99(pkCol, Col1)
SELECT 1, @x + @y

--Update the Data
UPDATE myTable99 
   SET Col1 = REPLACE(SUBSTRING(Col1,1,8000),'x','a') + REPLACE(SUBSTRING(Col1,8001,8000),'y','a')
 WHERE pkCol = 1 
GO

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-02 : 09:12:18

quote:
Originally posted by brendalisalowe

Everything works fine, except when I do this:

update tblparadox
set notes = REPLACE(SUBSTRING(notes,8001,8000),'"','')

When I do that, it deletes everything. Do you know why?

Brenda

If it weren't for you guys, where would I be?

Yes, I do now...because I'm an idiot....

See what not thoroughly testing does...

Its seems that even though you concat any varchar values, your still limited to the max size of varchar(8000)

For example


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(pkCol int, Col1 text)
GO

DECLARE @x varchar(8000), @y varchar(8000)
SELECT @x = REPLICATE('x',8000), @y = REPLICATE('y',8000)

-- mimic the load
INSERT INTO myTable99(pkCol, Col1)
SELECT 1, @x + @y

-- How long?

SELECT LEN(@x+@y)

SELECT DATALENGTH(Col1) FROM myTable99

SELECT SUBSTRING(Col1,8001,1) FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

I'm liking my idea of many rows even better...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-02 : 14:24:30

What did you do?

Modify the text file?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources