Replacing " with nothing

Question

Replacing " with nothing

machzy

Old Hand

Points: 355
More actions
July 19, 2018 at 7:56 am

#354819

Trying to replace any instances of " with nothing.
When I try this expression REPLACE(Fields!Name.Value,""","") I get this error:
The Value expression for the textrun 'Textbox12.Paragraphs[0].TextRuns[0]' contains an error: [BC30648] String constants must end with a double quote.
When I try this expression REPLACE(Fields!Name.value," ","") it works. So I'm not sure what I need to do to get it to remove double quotes.
Thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

below86 SSChampion Points: 11532 More actions · Answer 1

I would suggest replacing the double quotes in your SQL statement that pulls the data instead of trying to do it in SSRS.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

machzy Old Hand Points: 355 More actions · Answer 2

I tried the following, and none worked:

REPLACE(Fields!Name.value,"\","")

REPLACE(Fields!Name.value,"\\","")

REPLACE(Fields!Name.value,chr(34),"")

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

machzy - Thursday, July 19, 2018 7:56 AM
Trying to replace any instances of " with nothing.
When I try this expression REPLACE(Fields!Name.Value,""","") I get this error:
The Value expression for the textrun 'Textbox12.Paragraphs[0].TextRuns[0]' contains an error: [BC30648] String constants must end with a double quote.
When I try this expression REPLACE(Fields!Name.value," ","") it works. So I'm not sure what I need to do to get it to remove double quotes.
Thanks.

You need to escape the quote because it's just closing your string. You could try one of the following options:
REPLACE(Fields!Name.Value,CHAR(34),"") REPLACE(Fields!Name.Value,"""","")

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

below86 SSChampion Points: 11532 More actions · Answer 4

If the field name is 'Name' that you are trying to do this on then change the SQL when you SELECT the data to do the replace.
Like: REPLACE(Name,'"','')
In the SQL it is a REPLACE(Name, single quote double quote single quote, single quote single quote)

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

machzy Old Hand Points: 355 More actions · Answer 5

Luis Cazares - Thursday, July 19, 2018 8:06 AM
machzy - Thursday, July 19, 2018 7:56 AM
Trying to replace any instances of " with nothing.
When I try this expression REPLACE(Fields!Name.Value,""","") I get this error:
The Value expression for the textrun 'Textbox12.Paragraphs[0].TextRuns[0]' contains an error: [BC30648] String constants must end with a double quote.
When I try this expression REPLACE(Fields!Name.value," ","") it works. So I'm not sure what I need to do to get it to remove double quotes.
Thanks.
You need to escape the quote because it's just closing your string. You could try one of the following options:
REPLACE(Fields!Name.Value,CHAR(34),"") REPLACE(Fields!Name.Value,"""","")

The CHAR(34) function gave me this error:
The Value expression for the textrun 'Textbox12.Paragraphs[0]. TextRuns[0]' contains an error: [BC30108] 'Char' is a type and cannot be used as an expression
The second one allows me to save the report, but doesn't replace the double quotes.

Thom A SSC Guru Points: 99130 More actions · Answer 6

machzy - Thursday, July 19, 2018 8:17 AM
Luis Cazares - Thursday, July 19, 2018 8:06 AM
You need to escape the quote because it's just closing your string. You could try one of the following options:
REPLACE(Fields!Name.Value,CHAR(34),"") REPLACE(Fields!Name.Value,"""","")
The CHAR(34) function gave me this error:
The Value expression for the textrun 'Textbox12.Paragraphs[0]. TextRuns[0]' contains an error: [BC30108] 'Char' is a type and cannot be used as an expression
The second one allows me to save the report, but doesn't replace the double quotes.

Think Luis accidentally use the T-SQL function, not the SSRS function (cause, they're not the same...) Use Chr:
Replace(Fields!Name.Value,Chr(34),"")
If that still isn't replacing the character, this implies the character isn't a ", and is something else. If so,. where is the data coming from? You might need to use ASCII or GetChar to find out what the actual character is.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

machzy Old Hand Points: 355 More actions · Answer 7

Thom A - Thursday, July 19, 2018 8:23 AM
machzy - Thursday, July 19, 2018 8:17 AM
Luis Cazares - Thursday, July 19, 2018 8:06 AM
You need to escape the quote because it's just closing your string. You could try one of the following options:
REPLACE(Fields!Name.Value,CHAR(34),"") REPLACE(Fields!Name.Value,"""","")
The CHAR(34) function gave me this error:
The Value expression for the textrun 'Textbox12.Paragraphs[0]. TextRuns[0]' contains an error: [BC30108] 'Char' is a type and cannot be used as an expression
The second one allows me to save the report, but doesn't replace the double quotes.
Think Luis accidentally use the T-SQL function, not the SSRS function (cause, they're not the same...) Use Chr:
Replace(Fields!Name.Value,Chr(34),"")
If they still isn't replacing the character, this implies the character isn't a ", and is something else. If so,. where is the data coming from? You might need to use ASCII or GetChar to find out what the actual character is.

Ah, in that case, I already tried Chr(34) and that didn't work.
Pretty sure it's a double quote. The report exports to CSV. When I open it in Notepad, I can do a Find & Replace for " and it finds them. Here's a screenshot

My problem is that I can't figure out the logic behind where and why it places them sporadically throughout my report.

I don't want to change the SELECT statement because I tried to simplify my problem, but the actual expression isn't just a single Name field, it's actually a pretty big expression. Was hoping that wouldn't make a difference, but maybe it does. Here's my expression (please don't judge how ugly or inefficient it is, I'm a super rookie):

="J" &

"12345" &

"1234" &

" " &

"1234567" &

switch(LEN(Cstr(Fields!CheckNum.Value))=1,"0000000000000" & Fields!CheckNum.Value, LEN(Cstr(Fields!CheckNum.Value))=2,"000000000000" & Fields!CheckNum.Value, LEN(Cstr(Fields!CheckNum.Value))=3,"00000000000" & Fields!CheckNum.Value, LEN(Cstr(Fields!CheckNum.Value))=4,"0000000000" & Fields!CheckNum.Value, LEN(Cstr(Fields!CheckNum.Value))=5,"000000000" & Fields!CheckNum.Value) &

switch(LEN(Cstr(Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1)))=1,"000000000" & Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1), LEN(Cstr(Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1)))=2,"00000000" & Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1), LEN(Cstr(Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1)))=3,"0000000" & Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1), LEN(Cstr(Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1)))=4,"000000" & Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1), LEN(Cstr(Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1)))=5,"00000" & Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1), LEN(Cstr(Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1)))=6,"0000" & Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1), LEN(Cstr(Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1)))=7,"000" & Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1), LEN(Cstr(Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1)))=8,"00" & Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1), LEN(Cstr(Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1)))=9,"0" & Left(Fields!CheckAmt.Value*100,InStr(Fields!CheckAmt.Value*100,".")-1) ) &

"ISSUED " &

Year(Fields!CheckDate.Value) & switch(Month(Fields!CheckDate.Value)<10,"0" & Month(Fields!CheckDate.Value), Month(Fields!CheckDate.Value)>9,Month(Fields!CheckDate.Value)) & switch(Day(Fields!CheckDate.Value)<10,"0" & Day(Fields!CheckDate.Value), Day(Fields!CheckDate.Value)>9,Day(Fields!CheckDate.Value)) &

" " &

switch(LEN(Fields!Calc_VendorID.Value)=1,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=2,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=3,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=4,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=5,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=6,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=7,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=8,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=9,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=10,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=11,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=12,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=13,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=14,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=15,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=16,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=17,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=18,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=19,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=20,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=21,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=22,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=23,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=24,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=25,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=26,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=27,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=28,Fields!Calc_VendorID.Value & " ", LEN(Fields!Calc_VendorID.Value)=29,Fields!Calc_VendorID.Value & " ") &

" " &

switch(LEN(Fields!Name.Value)=1,Fields!Name.Value & " ", LEN(Fields!Name.Value)=2,Fields!Name.Value & " ", LEN(Fields!Name.Value)=3,Fields!Name.Value & " ", LEN(Fields!Name.Value)=4,Fields!Name.Value & " ", LEN(Fields!Name.Value)=5,Fields!Name.Value & " ", LEN(Fields!Name.Value)=6,Fields!Name.Value & " ", LEN(Fields!Name.Value)=7,Fields!Name.Value & " ", LEN(Fields!Name.Value)=8,Fields!Name.Value & " ", LEN(Fields!Name.Value)=9,Fields!Name.Value & " ", LEN(Fields!Name.Value)=10,Fields!Name.Value & " ", LEN(Fields!Name.Value)=11,Fields!Name.Value & " ", LEN(Fields!Name.Value)=12,Fields!Name.Value & " ", LEN(Fields!Name.Value)=13,Fields!Name.Value & " ", LEN(Fields!Name.Value)=14,Fields!Name.Value & " ", LEN(Fields!Name.Value)=15,Fields!Name.Value & " ", LEN(Fields!Name.Value)=16,Fields!Name.Value & " ", LEN(Fields!Name.Value)=17,Fields!Name.Value & " ", LEN(Fields!Name.Value)=18,Fields!Name.Value & " ", LEN(Fields!Name.Value)=19,Fields!Name.Value & " ", LEN(Fields!Name.Value)=20,Fields!Name.Value & " ", LEN(Fields!Name.Value)=21,Fields!Name.Value & " ", LEN(Fields!Name.Value)=22,Fields!Name.Value & " ", LEN(Fields!Name.Value)=23,Fields!Name.Value & " ", LEN(Fields!Name.Value)=24,Fields!Name.Value & " ", LEN(Fields!Name.Value)=25,Fields!Name.Value & " ", LEN(Fields!Name.Value)=26,Fields!Name.Value & " ", LEN(Fields!Name.Value)=27,Fields!Name.Value & " ", LEN(Fields!Name.Value)=28,Fields!Name.Value & " ", LEN(Fields!Name.Value)=29,Fields!Name.Value & " ", LEN(Fields!Name.Value)=30,Fields!Name.Value & " ", LEN(Fields!Name.Value)=31,Fields!Name.Value & " ", LEN(Fields!Name.Value)=32,Fields!Name.Value & " ", LEN(Fields!Name.Value)=33,Fields!Name.Value & " ", LEN(Fields!Name.Value)=34,Fields!Name.Value & " ", LEN(Fields!Name.Value)=35,Fields!Name.Value & " ", LEN(Fields!Name.Value)=36,Fields!Name.Value & " ", LEN(Fields!Name.Value)=37,Fields!Name.Value & " ", LEN(Fields!Name.Value)=38,Fields!Name.Value & " ", LEN(Fields!Name.Value)=39,Fields!Name.Value & " ", LEN(Fields!Name.Value)=40,Fields!Name.Value & " ", LEN(Fields!Name.Value)=41,Fields!Name.Value & " ", LEN(Fields!Name.Value)=42,Fields!Name.Value & " ", LEN(Fields!Name.Value)=43,Fields!Name.Value & " ", LEN(Fields!Name.Value)=44,Fields!Name.Value & " ", LEN(Fields!Name.Value)=45,Fields!Name.Value & " ", LEN(Fields!Name.Value)=46,Fields!Name.Value & " ", LEN(Fields!Name.Value)=47,Fields!Name.Value & " ", LEN(Fields!Name.Value)=48,Fields!Name.Value & " ", LEN(Fields!Name.Value)=49,Fields!Name.Value & " ", LEN(Fields!Name.Value)=50,Fields!Name.Value & " ") &

" " &

" "

machzy Old Hand Points: 355 More actions · Answer 8

Also, what I was trying to do is wrap REPLACE around that whole thing of code above.

machzy Old Hand Points: 355 More actions · Answer 9

Okay, I figured out the logic of when this happens. Whenever the Vendor name contains a comma, it screws up my expression and adds in the double quotes.

Thom A SSC Guru Points: 99130 More actions · Answer 10

machzy - Thursday, July 19, 2018 8:31 AM
Thom A - Thursday, July 19, 2018 8:23 AM
Think Luis accidentally use the T-SQL function, not the SSRS function (cause, they're not the same...) Use Chr:
Replace(Fields!Name.Value,Chr(34),"")
If they still isn't replacing the character, this implies the character isn't a ", and is something else. If so,. where is the data coming from? You might need to use ASCII or GetChar to find out what the actual character is.
Ah, in that case, I already tried Chr(34) and that didn't work.
Pretty sure it's a double quote. The report exports to CSV. When I open it in Notepad, I can do a Find & Replace for " and it finds them. Here's a screenshot

How do you propose I copy that text? It's an image.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

machzy Old Hand Points: 355 More actions · Answer 11

Thom A - Thursday, July 19, 2018 8:48 AM
machzy - Thursday, July 19, 2018 8:31 AM
Thom A - Thursday, July 19, 2018 8:23 AM
Think Luis accidentally use the T-SQL function, not the SSRS function (cause, they're not the same...) Use Chr:
Replace(Fields!Name.Value,Chr(34),"")
If they still isn't replacing the character, this implies the character isn't a ", and is something else. If so,. where is the data coming from? You might need to use ASCII or GetChar to find out what the actual character is.
Ah, in that case, I already tried Chr(34) and that didn't work.
Pretty sure it's a double quote. The report exports to CSV. When I open it in Notepad, I can do a Find & Replace for " and it finds them. Here's a screenshot

How do you propose I copy that text? It's an image.

Oops, sorry about that. Didn't know you wanted to copy it. Here's the text:

Textbox12,Textbox17
J878660002
J878660002
J878660002
J878660002
J878660002
J878660002
J878660002
J878660002
J878660002
"J878660002
J878660002
J878660002
J878660002
J878660002
J878660002

Larry Huisingh SSC Enthusiast Points: 196 More actions · Answer 12

machzy - Thursday, July 19, 2018 8:46 AM
Okay, I figured out the logic of when this happens. Whenever the Vendor name contains a comma, it screws up my expression and adds in the double quotes.

Based on this discovery I would look at fixing the root cause of the extra double quotes, if possible. Look at modifying the process that adds in the double quotes. Maybe search the Vendor names for commas and handle it differently (e.g. remove commas, add a backslash before the comma).