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?

Gabriele Petrioli · Accepted Answer · 2010-02-16 22:13:28Z

14

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.

answered Feb 16, 2010 at 22:13

Gabriele Petrioli

197k35 gold badges275 silver badges331 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

KM. · Accepted Answer · 2010-02-16 22:18:43Z

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

answered Feb 16, 2010 at 22:18

KM.

104k34 gold badges183 silver badges214 bronze badges

Comments

rosscj2533 · Accepted Answer · 2010-02-16 22:12:00Z

1

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

answered Feb 16, 2010 at 22:12

rosscj2533

9,3437 gold badges42 silver badges58 bronze badges

Collectives™ on Stack Overflow

T-SQL String Replace

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related