Removing Quotes From String

Question

I am passing a parameter to a dynamic sql string and have the parameter properly encompassed in ' marks to work in my dynamic sql. However, now I am needing to use the value in a diff location in my code, and want to remove all the ' marks. I tried the Replace() function, but I must be using incorrect syntax as it still leaves the string with single quotes on each side. Take the sample DDL Below

DECLARE @Param1 nvarchar(500) = N'''Blue Bell Ice Cream''';
PRINT REPLACE(@Param1, '''''', '')

Prints this

'Blue Bell Ice Cream'

My desired result to be returned is

Blue Bell Ice Cream

How should I write the REPLACE() function in order to remove all single quotes?

Answer 1

You are using 6 quotes, this is not correct, you are trying to replace every single quote ', then use only a double quote '' to indicate the string pattern.

If you use '''' four quotes you are really searching for a double ''

REPLACE(@Param1, '''', '')