SSRS Report Returning Double Quote string from a Single Quote String

Hi, I'm getting weird thing in resultset from SSRS report when executed. When I pass parameter to a report, which passes String that has single quote value to a split function , it returns rows with double quote. 

For example  following string:

'N gage, Wash 'n Curl,Murray's, Don't-B-Bald

Returns: 

''N gage, Wash ''n Curl,Murray''s, Don''t-B-Bald

through SSRS report.

Here is the split function Im using in a report.

CREATE Function [dbo].[fnSplit] (
@List varchar(8000), 
@Delimiter char(1)

Returns @Temp1 Table (
ItemId int Identity(1, 1) NOT NULL PRIMARY KEY , 
Item varchar(8000) NULL 

As 
Begin 
Declare @item varchar(4000), 
@iPos int 

Set @Delimiter = ISNULL(@Delimiter, ';' ) 
Set @List = RTrim(LTrim(@List)) 

-- check for final delimiter 
If Right( @List, 1 ) <> @Delimiter -- append final delimiter 
Select @List = @List + @Delimiter -- get position of first element 
Select @iPos = Charindex( @Delimiter, @List, 1 ) 
While @iPos > 0 
Begin 
-- get item 
Select @item = LTrim( RTrim( Substring( @List, 1, @iPos -1 ) ) ) 

If @@ERROR <> 0 Break -- remove item form list 
Select @List = Substring( @List, @iPos + 1, Len(@List) - @iPos + 1 ) 
If @@ERROR <> 0 Break -- insert item 
Insert @Temp1 Values( @item ) If @@ERROR <> 0 Break 
-- get position pf next item 
Select @iPos = Charindex( @Delimiter, @List, 1 ) 
If @@ERROR <> 0 Break 
End 
Return 
End

FYI: I'm getting @List value from a table and passing it as a string to split function. 

Any help would be apprec

February 21st, 2015 3:34am

Another user from TSQL forum posted this code which is returning the same resultset but when I execute both codes in SQL server it works and return single quote as expected.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8d5c96f5-c498-4f43-b2fb-284b0e83b205/passing-string-which-has-single-quote-rowvalue-to-a-function-returns-double-quoate?forum=transactsql

CREATE FUNCTION dbo.splitter(@string VARCHAR(MAX), @delim CHAR(1))
RETURNS @result TABLE (id INT IDENTITY, value VARCHAR(MAX))
AS
BEGIN
 WHILE CHARINDEX(@delim,@string) > 0
  BEGIN
   INSERT INTO @result (value) VALUES (LEFT(@string,CHARINDEX(@delim,@string)-1))
   SET @string = RIGHT(@string,LEN(@string)-CHARINDEX(@delim,@string))
  END
   INSERT INTO @result (value) VALUES (@string)

RETURN
END
GO

Free Windows Admin Tool Kit Click here and download it now
February 21st, 2015 4:20am

fixed the issue by using Replace function like

Replace(value,'''''','''')

February 21st, 2015 7:18am

Hi SQL_Admirer,

Glad to heard that the issue had been solved and thanks for your sharing. It will be very beneficial for other community members who have similar questions.

If you have any more questions, please feel free to ask.

Thanks,
Wendy Fu

If you have any feedback on our support, please click here.

Free Windows Admin Tool Kit Click here and download it now
February 22nd, 2015 6:29am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics