Replace with unicode (SQL 7) - 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)

Replace with unicode (SQL 7)

Author

Topic

Pika
Starting Member

9 Posts

Posted - 2003-03-17 : 07:30:36

What is the unicode equivalent of the REPLACE function?

We used REPLACE in a CHAR column.
After an upgrade the column is now ntext an REPLACE does not work anymore.

Any suggestions? Thanks!

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-03-17 : 08:07:21

Is this really a problem with Unicode? You say that it was a char column and now it's ntext -- REPLACE won't work directly on text or ntext.

Pika
Starting Member

9 Posts

Posted - 2003-03-17 : 08:28:49

This is the errormessage:

Argument data type ntext is invalid for argument 1 of replace function.

If you want to re-create the problem, try this:

create table tbUnicodeTest (Ntest ntext)
insert tbUnicodeTest (Ntest) values ('Part one' + char(10) + 'part two' + char(13) + 'part three.')

select * from tbUnicodeTest

declare @Return1 nvarchar(1)
declare @Return2 nvarchar(1)
declare @Space nvarchar(1)
set @Return1 = CHAR(10) /* enter */
set @Return2 = CHAR(13) /* enter */
set @Space = CHAR(32) /* blank */

update tbUnicodeTest
set Ntest = REPLACE(Ntest, @Return1, @Space) /* replace enter by blank */

update tbUnicodeTest
set Ntest = REPLACE(Ntest, @Return2, @Space) /* replace enter by blank */

drop table tbUnicodeTest

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-03-17 : 09:43:54

You can only use replace with:

char
varchar
nchar
nvarchar

not with text etc.

Pika
Starting Member

9 Posts

Posted - 2003-03-17 : 09:49:40

That is exactly the problem.
Is there some way around this?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-17 : 10:21:25

quote:
That is exactly the problem.
Is there some way around this?

You've got a square peg and a round hole. You will either have to use the functions available for working with a text data type or you'll have to convert your data to a datatype that replace will work with.

Jay White
{0}

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-03-17 : 11:47:02

Look into these functions which are usable with text datatype:

substring
patindex
datalength

You should be able to accomplish what you want using them.

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-17 : 16:31:16

How about....why are you using ntext in the first place?

Brett

8-)

Pika
Starting Member

9 Posts

Posted - 2003-03-18 : 03:08:16

quote:
How about....why are you using ntext in the first place?

ok, some explanation. The database is used with a VB frontend. These are both developed by an other company. They decided to use ntext instead of char. We use the database to get management information.

I'll see wat I can do with

quote:
substring patindex datalength

Thanks.

Edited by - Pika on 03/18/2003 03:09:10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources