REPLACE returns varchar(8000) result - why? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 returns varchar(8000) result - why?

Author

Topic

jones_d
Yak Posting Veteran

61 Posts

Posted - 2004-05-07 : 04:53:15

Hi,

I have created a table with a field (DESC1) defined as varchar(20).

I am selecting this field into another table using a statement similar to: select REPLACE([DESC1],'¤','€') INTO ft_ledger from sa_ledger

However, the result returned after using the REPLACE function is varchar(8000) in the new table. I would have expected it to be varchar(20) as in the original table.

Does anyone know why the result field is varchar(8000)? I realise that this doesn't make a big difference because it isn't going to take any extra space in the database - just wondering why it is happening.

Many Thanks,

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-05-07 : 05:11:19

I didn't know that.
I tried it now myself and I see its true!

I couldn't find anything in Books Online about it. I suppose this is the case because SQL server can never be sure of exactly how the replace is going to work - i mean after all you could replace 'A' with 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' - so the VARCHAR(8000) is created because 8000 is the max length allowed in SQL Server for a varchar field.

There are obviously ways to get around this by using cast or by creating the destination table first and then doing insert into as opposed to select into - like done below into #test3

--***********************************************************
create table #test(Field1 VARCHAR(50))

insert into #test values('ABCDEFG')

select replace(Field1, 'C', 'c') as Field2
into #test2
from #test

CREATE TABLE #test3(Field1 VARCHAR(50))
insert into #Test3
select replace(Field1, 'C', 'c')
from #test

Duane.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-05-07 : 06:09:41

The REPLACE function in SQL Server always returns a result of datatype varchar(8000), unless you explicitly cast it to a smaller length... and for a good reason. If were to call the function asking it to replace a single character with more than one, you could end up with a string that was longer than the original. Then what should the function return?

For example:


DECLARE @MyString VARCHAR(10)
SET @MyString = 'Hello Dear'

SELECT REPLACE(@MyString, 'e', 'abc')

If the above code returned a value of varchar(10) it would be truncated.

OS

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-07 : 06:20:50

Replace is a bit buggy and you usually need to convert the result.
If you replace on a char field it will rtrim whereas with a varch it doesn't which is a bit odd.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jones_d
Yak Posting Veteran

61 Posts

Posted - 2004-05-07 : 07:34:51

That seems like a logical explanation.

Thanks for your replies

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources