November 13, 2015 at 12:19 am
To debug replace
EXEC (@SQL)
with
PRINT @SQL
You will see the SQL it generates.
You can copy a line back into a SQL Server Management Studio query window, and run it (or see whether SSMS' parser detects a syntax error).
Next, refer to String Functions (Transact-SQL) and Wildcards in Transact-SQL. Your rewritten SET @SQL = string expression should probably use LIKE, %, LEFT, and CHARINDEX. Remember to escape apostrophes within the @sql string, by doubling them.
It would be best to not[/I] use QUOTENAME the way the author is using it with @SearchStr and @ReplaceStr:crazy:. QUOTENAME returns NVARCHAR(258), because it is designed to handle schema-qualified object names (==not all strings). You can expect QUOTENAME to fail (truncate results), when its string argument is greater than 258 characters. A more robust solution is to simply use escaped apostrophes within @sql's string, on either side of concatenations with @SearchStr and @ReplaceStr.
November 17, 2015 at 5:19 am
You can also use "SELECT @sql" which will do the same thing as PRINT but just stick it in the RESULTS tab instead of the MESSAGES tab.
November 24, 2015 at 11:21 am
Thanks,
I've been working on this search/replace string and had a question about why the replace still is not occurring.
Below is the actual SQL I've been using in test:
UPDATE [dbo].[tbl_a]
SET [a_col] = REPLACE([a_col],'</title>%','')
WHERE [a_col] LIKE '%</title>%'
When used in a SELECT statement, the 'LIKE' clause, as above, does find the occurrences of the '%</title>%' string; however, no replace occurs.
There is a message, however, stating that x number of rows was affected.
I would like for all text, beginning with "</title>," through the end of the string, to be replaced with nothing ('').
Are my escaped characters in need of editing?
Thanks 🙂
November 24, 2015 at 11:26 am
Your escape characters need escaping. @=)
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply