none
using REPLACE to replace ' quotes RRS feed

  • Question

  • Hello there, i am currently passing in a parameter to a stored procedure which could look something like this for example:
    ''a'',''b'',''b''

    What i would like to do is remove the '' and place it with a single ' so it looks something like:
    'a'','b','b'

    However if i do this:
    (REPLACE(@clients, '''','''))
    It obviously does not work since it is recognising each ' as an opening quotation

    The reason for the duplicate single quotes is just the way the software i am using is passing them in.

    Any ideas on how to get around this?

    Any help would be greatly appreciated.
    Monday, November 17, 2008 5:59 PM

All replies

  • You have to double up single quotes when you want to use them as literals.

    i.e.

    Code Snippet

    SELECT Replace(@clients, '''''', '''')


    Monday, November 17, 2008 6:03 PM
    Answerer
  • i see, so doubling up and having Replace(@clients, '''''', '''') would convert e.g. ''a'' into 'a' ?

    Thanks
    Monday, November 17, 2008 6:18 PM
  •  cagdasal wrote:
    i see, so doubling up and having Replace(@clients, '''''', '''') would convert e.g. ''a'' into 'a' ?

    Thanks

    Correct.  I believe it is much easier to use the character number instead of the single quote.  It looks cleaner and is eaiser to understand.  Both methods work fine though.

     

    Code Snippet

    declare @var varchar(500)

    set @var = '''''a'''',''''b'''',''''c'''''

     

    select replace(@var,char(39)+Char(39),Char(39))

     

    Monday, November 17, 2008 6:39 PM
    Moderator
  • Hello, thanks, the above works, but is there any reason why this doesnt work? :

     WHERE table.column in (REPLACE(@var, char(39)+Char(39),Char(39))) AND

    Monday, November 17, 2008 7:28 PM
  •  cagdasal wrote:
    Hello, thanks, the above works, but is there any reason why this doesnt work? :

     WHERE table.column in (REPLACE(@var, char(39)+Char(39),Char(39))) AND

    Yes.  The code does not work because, you cannot dynamically build an array and use it in an IN statement.  You will have to build a function to parse the string or use dynamic SQL.

     

    Take a look at the following post, as it deals with the same topic.

    http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=4136774&SiteID=1

    Monday, November 17, 2008 7:51 PM
    Moderator