How to replace semicolons?

Question

I have an SQL SELECT query that's grabbing some data from my database. I need to replace a certain word that contains a semicolon in my SELECT query. Exactly this:

REPLACE(Table.Field,'"','') AS Field1

The error I'm getting reads

Unclosed quotation mark after the character string '"'.

So I think the semicolon is terminating the query. How can I escape that semicolon?

I tried backslashes and using double quotes.

Some sample data and expected output, as requested

---

Sample data

Field
"Hello"
"Goodbye"

---

Expected output

Field1
Hello
Goodbye

---

Full Query

SELECT REPLACE(Table.Name,';','') AS Name,
    SUM(Table.Quantity) AS Quantity, 
    SUM(Table.Price*Table.Quantity) AS Price 
FROM Table 
GROUP BY Name

Answer 1

The ; symbol doesn't terminate the query and it should not be escaped, if it is part of the string literal (the text enclosed in single quotes ').

Here is a complete example that demonstrates that it works fine in SSMS:

CREATE TABLE #TempTable (Name varchar(50));

INSERT INTO #TempTable (Name) VALUES('Field');
INSERT INTO #TempTable (Name) VALUES('"Hello"');
INSERT INTO #TempTable (Name) VALUES('"Goodbye"');

SELECT
    Name
    ,REPLACE(Name,'"','') AS ReplacedName
FROM #TempTable;

DROP TABLE #TempTable;

This is the result set:

Name                   ReplacedName
----                   ------------
Field                  Field
"Hello"      Hello
"Goodbye"    Goodbye

You didn't provide all details of how you construct and execute your query, so I have a guess. It looks like you are:

• building the text of the query dynamically
• use some web-based tools/languages/technologies for that
• web-based text processing tool/language that you use parses the text of your SQL query as if it was HTML and interferes with the result. For one thing, it changes " to the " symbol.
• during all this processing you end up with unmatched ' symbol in the text of your SQL. It could come from the user input that you concatenate to your query of from a value stored in your database.
• it has nothing to do with the ; symbol. Your error message clearly states that the matching quotation mark (which is ') is missing after the " symbol.

To understand what is going on you should print out the text of the actual SQL query that is sent to the server. Once you have it, it should become obvious what went wrong. I don't think that the Full Query that you put in the question is the real query that you are trying to run. It has syntax error. So, get the real thing first.

Answer 2

This works fine for me

declare @a as nvarchar(50) = '"Hello"'
select REPLACE(@a,'"','') AS Field1

declare @b as nvarchar(50) = '"Goodbye"'
select REPLACE(@b,'"','') AS Field1

Error message says unclosed quotation mark ?

Do you have single quotes in few of your fields ? In that case you can replace them first as below

REPLACE(Table.Field,'''','') AS Field1

Let me know you need more help with this.

Answer 3

• Source

"  
    the double quote sign "

I think there is no where that this parameter is known as a special phrase that refers to " and cause you error message.

---

In SQL Server there is just a function like QUOTENAME ( 'character_string' [ , 'quote_character' ] ) that used like this: -Just for ' or " or []-

SELECT QUOTENAME('Sample', '"') --> result is `"Sample"`
SELECT QUOTENAME('Sam"ple', '"') --> result is `"Sam""ple"`

---

In SQL Server identifiers can be delimited by ", When SET QUOTED_IDENTIFIER is ON -for following the ISO rules-. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks.

I suggest you using SET QUOTED_IDENTIFIER OFF that make sure, that you've not identifier between " in your query.

Note:
When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the table's metadata even if the option is set to OFF when the table is created.

---

If you are using a SQL string I suggest this syntax:

REPLACE(Table.Field, CHAR(34), '') As Field1

or

REPLACE(REPLACE(Table.Field, ';', '.'), '&quot.', '') As Field1