Conversion from ntext to nvarchar datatype

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)

Conversion from ntext to nvarchar datatype

Author

Topic

niradjoshi
Starting Member

21 Posts

Posted - 2005-12-03 : 15:20:26

Hi Friends,

i want to insert field with ntext datatype to nvarchar datatype.

e.g. table1 with text(field_name) ntext(datatype)
table2 with vartext(field_name) nvarchar(datatype)

now how to execute below query?

insert into table2(vartext) select text from table1"

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-12-03 : 16:03:27

What error are you getting???

quote:

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'djl_test_text_conversion' AND xtype = 'U')
BEGIN
DROP TABLE djl_test_text_conversion
END

CREATE TABLE djl_test_text_conversion(
ntext_col NTEXT,
nvarchar_col NVARCHAR(255))

INSERT djl_test_text_conversion(ntext_col) VALUES('This is a test of the emergency broadcasting system.')

INSERT djl_test_text_conversion(nvarchar_col)
SELECT ntext_col FROM djl_test_text_conversion

SELECT * FROM djl_test_text_conversion

^^^ That code works fine. If your ntext is greater than the maximum length of the nvarchar, you will have to either truncate the remaining data on insert or use the readtext function which you can find in Books Online. Post your exact error and some DDL/DML if you want really good help on the issue.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

niradjoshi
Starting Member

21 Posts

Posted - 2005-12-03 : 17:10:58

Thank you for your guidance.

Now i am able to read that field using this code

USE pubs
go
DECLARE @msg varbinary(16)
SELECT @msg = TEXTPTR(text)
FROM message where id =@id
READTEXT message.text @msg 0 289
GO

Now another issue ahead. i have one pattern stored in @upmessage variable.

and i want to compare this READTEXT message with @upmessage pattern LIKE

e.g
if(READTEXT message.text @msg 0 289 like @upmessage )
begin
My Code
end

can you please help me out?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-12-04 : 13:11:43

Give us some sample code that works in the format I provided above. We can then help you out. That way we don't have to invent something on our own each time. That's a good practice to always follow when posting for help on SQL or development forums.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources