String manipulation - 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

Transact-SQL (2000)

String manipulation

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-02 : 03:58:12

OldTable.ContactNote TEXT
EventHistory.EVH_Note VARCHAR(4000)

In this query I want to take all the characters from position 8001 to 12000 and put them in my varchar4000 column:

INSERT INTO EventHistory (EVH_HistoryDate, EVH_Desc, EVH_Note)
SELECT A.ContactDateTime, A.Summary, SUBSTRING(A.ContactNote, 8001, 12000)
FROM OldTable A
WHERE DATALENGTH(A.ContactNote) > 8000
GO

But I get: "Expression result length exceeds the maximum. 8000 max, 12000 found."

Is that a limit of the SUBSTRING function? or what?

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-02 : 04:13:33

Look up READTEXT in BOL

Andy

Beauty is in the eyes of the beerholder

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-03 : 04:22:07

Deary me! That looks a lot more complicated and a WHOLE LOT SLOWER. Is the only way to do this then:
1) Declare a variable for the text pointer
2) Loop through the table

I hope not

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-03 : 08:42:48

I'm working with a table with millions of records. Can this really not be done in one SQL statement without variables and loops?

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-03 : 11:19:39

quote:
Originally posted by coolerbob

I hope not

Episode IV: A New Hope

Use The Force Bob

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-03 : 11:43:05

You told SUBSTRING to return 12,000 characters. You need to change the second paramater, it is the length, not the character position.

Change:
SUBSTRING(A.ContactNote, 8001, 12000)

To:
SUBSTRING(A.ContactNote, 8001,  4000)

quote:
Originally posted by coolerbob

OldTable.ContactNote TEXT
EventHistory.EVH_Note VARCHAR(4000)

In this query I want to take all the characters from position 8001 to 12000 and put them in my varchar4000 column:

INSERT INTO EventHistory (EVH_HistoryDate, EVH_Desc, EVH_Note)
SELECT A.ContactDateTime, A.Summary, SUBSTRING(A.ContactNote, 8001, 12000)
FROM OldTable A
WHERE DATALENGTH(A.ContactNote) > 8000
GO

But I get: "Expression result length exceeds the maximum. 8000 max, 12000 found."

Is that a limit of the SUBSTRING function? or what?

CODO ERGO SUM

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-03 : 12:14:40

Is Brett always cracking jokes??
Thanks Michael, that was REAL stupid of me.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-03 : 12:30:12

quote:
Originally posted by coolerbob

Is Brett always cracking jokes??
Thanks Michael, that was REAL stupid of me.

Not always....

What Mikey fails to mention is the logging you're going to incur...

What's millions?

If it's really alot...I would bcp the data out using queryout, then bcp the data back in to the table.

And Bob, could you hand me a cold one from your cooler?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

EDIT: Like this


USE Northwind
GO

EXEC master..xp_cmdshell 'bcp "SELECT SUBSTRING(Notes,80001,4000) FROM Northwind.dbo.Employees" queryout c:\Test.txt -S -Usa -P -c'

And you thought that was a joke? Why thank you...

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-29 : 04:32:20

But if I do that Brett, would I be able to preserve the data that is already in the table when I BCP the additional data in?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources