CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
pau.
pau.

Reputation: 83

T-SQL String Replace

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?

Upvotes: 8

Views: 13821

Answers (3)

KM.
KM.

Reputation: 103597

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

Upvotes: 2

Gabriele Petrioli
Gabriele Petrioli

Reputation: 196002

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.

Upvotes: 14

rosscj2533
rosscj2533

Reputation: 9323

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))

Upvotes: 1

Related Questions