T-SQL replacement

Question

I need to replace the {URL} tag:

DECLARE @PageUrl varchar(200)
DECLARE @Body varchar(MAX)

SET @PageUrl = 'http://www.website.com/site1/site2/pageName.asxp?rid=1232'
SET @Body = 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed luctus, 
{URL} enim nec posuere volutpat, neque dui volutpat turpis. '

SET @Body = REPLACE(@Body,'{Url}', CONVERT(varchar,@PageUrl))
PRINT @Body

Expected Result:

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed luctus,
http://www.website.com/site1/site2/pageName.asxp?rid=1232 enim nec posuere volutpat, neque dui volutpat turpis.

And print result:

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed luctus,
http://www.website.com/site1/s enim nec posuere volutpat, neque dui volutpat turpis.

As you can see, the replace function shortens the url string to its 31 ...

What am I doing wrong?

Answer 1

The problem is not in the method replace, but in the method convert.

You need to specify the length of the converted type

SET @Body = REPLACE(@Body,'{Url}', CONVERT(varchar(200),@PageUrl))

or since it is already defined as varchar, just use the variable ..

SET @Body = REPLACE(@Body,'{Url}', @PageUrl)

If you look at char / vachrar

If n is not specified in the data definition or declaration of a variable, the default length is 1. When n is not specified when using the CAST and CONVERT Functions, the default length is 30 .

Answer 2

varchar:

CONVERT(varchar,@PageUrl)

, ? 30, (31- char )

:

CONVERT(varchar(200),@PageUrl)

Answer 3

It is cropped on this line:

SET @Body = REPLACE(@Body,'{Url}', CONVERT(varchar,@PageUrl)) 

Use this:

SET @Body = REPLACE(@Body,'{Url}', CONVERT(varchar(200),@PageUrl))