T-SQL String Replace

Question

I need to replace the tag {URL}:

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

My expected result is:

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 the print result is:

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 cuts the url string at its 31...

What I'm doing wrong?

Solution 1

The problem is not the replace method , it is the convert method..

You need to either specify the length of the converted type

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

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

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

If you have a look at the char/vachrar page

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

Solution 2

You don't specify a varchar length:

CONVERT(varchar,@PageUrl)

guess what the default is? 30, just like you say in the question (the 31st char to the end is missing)

use it like this:

CONVERT(varchar(200),@PageUrl)

to get all the characters

Solution 3

It's getting cut off at this line:

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

Use this:

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