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 2008 Forums
 Transact-SQL (2008)
 Varchar(max)

Author  Topic 

Clages1
Yak Posting Veteran

69 Posts

Posted - 2013-04-27 : 18:25:54
Hi

declare @x varchar(max)

select @ax = REPLICATE('a',2000)
select @bx = REPLICATE('b',2000)
select @cx = REPLICATE('c',2000)
select @dx = REPLICATE('d',2000)
select @ex = REPLICATE('e',2000)

select @x = @ax + @bx + @cx + @dx + @ex

why this select SUBSTRING(@x,9000,150) dont bring to me 'eeeeeee'?
where is my mistake?
tks
Clages



Clages1
Yak Posting Veteran

69 Posts

Posted - 2013-04-27 : 18:44:11
Hi its me again
I found this solution.

set @x = Cast(@ax as Varchar(max)) +
Cast(@bx as Varchar(max)) +
Cast(@cx as Varchar(max)) +
Cast(@dx as Varchar(max)) +
Cast(@ex as Varchar(max))
select SUBSTRING(@x,9000,150)

now works fine,
is there another solution?
tks again

CLages
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-04-27 : 22:58:43
is @ax to @ex also varchar(max) ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Clages1
Yak Posting Veteran

69 Posts

Posted - 2013-04-29 : 12:02:30
No, they are @Ax to @ex varchar(8000)
tks
Clages
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-29 : 12:14:00
Think of it as computing the right hand side (using the data types it is given) and then assigning the result to the left hand side variable. Since all the variables on the right hand side are varchar(8000), the intermediate result it calculates is also varchar(8000).

You can get around this by doing what you did, or even simply this, which forces the intermediate result to varchar(max)

SELECT @x = CAST(@ax AS VARCHAR(MAX)) + @bx + @cx + @dx + @ex
Go to Top of Page
   

- Advertisement -