Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 996
pau. Avatar asked Feb 16 '10 22:02

pau.


People also ask

How do I replace a specific string in SQL?

SQL Server REPLACE() FunctionThe REPLACE() function replaces all occurrences of a substring within a string, with a new substring. Note: The search is case-insensitive. Tip: Also look at the STUFF() function.

How do you replace in SQL?

The basic syntax of replace in SQL is: REPLACE(String, Old_substring, New_substring); In the syntax above: String: It is the expression or the string on which you want the replace() function to operate.

How do I replace multiple characters in a string in SQL?

If you wanted to replace the words with blank string, go with REGEXP_REPLACE() . If you want to replace the words with other words, for example replacing & with and then use replace() . If there are multiple words to be replaced, use multiple nested replace() .

How find and replace in SQL query?

On the Edit menu, point to Find and Replace, and then click Quick Replace to open the dialog box with both find options and replace options. Toolbar buttons and shortcut keys are also available to open the Find and Replace dialog box.


1 Answers

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.

like image 57
Gabriele Petrioli Avatar answered Oct 08 '22 22:10

Gabriele Petrioli