0

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?

  • have you tried with: REPLACE(@Param1, '''', '') or REPLACE(@Param1, '\'', '') – McNets Nov 26 '16 at 1:43
  • @mcNets - why does your example of '''' work, but mine with '''''' does not and the string has 6 single quotes surrounding it? And the second example produces an error of Incorrect syntax near '\', ') – StarsFlyFree FromCozyNights Nov 26 '16 at 1:45
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.