Modify column type - 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)

Modify column type

Author

Topic

Phibian
Starting Member

32 Posts

Posted - 2003-04-14 : 14:50:25

I want to modify a column from ntext to nvarchar. Can it be done? If so, how?

Tx

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-14 : 15:10:36

Well since ntext can hold quite a bit of data:

quote:

BOL:

Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.

I would say you've got a lot of parsing to worry about. And you'll be putting the ntext data probably in to multiple rows.

Also there's always the fun in dealing with pointers and the like:

See BOL for READTEXT, WRITETEXT

Good Luck

Brett

8-)

Phibian
Starting Member

32 Posts

Posted - 2003-04-14 : 15:33:46

Hating to show my ignorance here, but I don't know what "BOL" is.

However, I can say that no parsing should be involved. The column was created as ntext inadvertently, and it is quite alright to "chop" it at 255 characters (actually, 50 would be fine).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-14 : 15:47:16

quote:

Hating to show my ignorance here, but I don't know what "BOL" is.

BOL = SQL Server Books Online - the SQL Server documentation

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-14 : 16:01:31

OK, Then how about trying this:

CREATE TABLE Table1 (col1 Text)
GO
INSERT INTO Table1 (col1) SELECT '123456789'
GO
INSERT INTO Table1 (col1) SELECT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
GO
SELECT MAX(LEN(CONVERT(varchar(255),col1))) FROM table1
GO
DROP TABLE Table1
GO

You can do the select SELECT statement against your table now to find out what the longest value is. Create a new column on your table, and update the new column with the convert portion of the SELECT.

If you want the either rename the existing column (or drop it) and name the new column to the old name. Shouldn't have to touch any existing code.

Good Luck

Brett

8-)

Edited by - x002548 on 04/14/2003 16:02:58

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources