Replace quote mark character in table using SQL

I am trying to do a simple find and replace query using MSSQL, however the query runs without removing the character.Keep in mine I want a query without having to list the unique data in each row.

Current row

    Number 
    ''12454545''
    ''12454''
    ''1895622''
    ''9846252''

Requited Output

    Number 
    12454545
    12454
    1895622
    9846252

Therefore I only need to remove the ('') from the value in each row. The find and replace function in Excel is exactly the principle required here.

Here are two of the queries I have tried that runs successfully, however it does not remove the ('') .

UPDATE [Table]
SET [Number] = REPLACE([Number], '''', '')

SELECT REPLACE([Number], '''', '') FROM [Table]

Any suggestions?

Answers


Try this:

UPDATE [Table]
SET [Number] = REPLACE([Number], '"', '')

This basically have the same impact as replacing two '', because each individual ' will be replaced.

I'm not sure why exactly its not working but I can guess its because how SQL-SERVER deals with quotes in a string and '''' is the same as '', so when you put an individual quote mark it will be recognized.


Okay, I think you can use SET QUOTED_IDENTIFIER OFF before your query something like this -

SET QUOTED_IDENTIFIER OFF;

UPDATE [Table]
SET [Number] = REPLACE([Number], "'", "")

SET QUOTED_IDENTIFIER ON;

Your reply to my comment suggests that you're dealing with ASCII character 34, which is a plain double quote ("), not the single quote (' character 39) that you're trying to replace with your current code.

Instead try:

UPDATE [Table] SET [Number] = REPLACE([Number], '"', '')

Or, to make it more obvious:

UPDATE [Table] SET [Number] = REPLACE([Number], CHAR(34), '')

Try

UPDATE [Table]
SET [Number] = REPLACE(REPLACE([Number],'''',''),'"','')

Need Your Help

How to install imagingft in OSX and Ubuntu?

python macos ubuntu python-imaging-library

I'm using PIL 1.1.6 to render a truetype font on my local Windows machine. I'd like to be able to get the application running in OSX and Ubuntu (server). So far the instructions I've found all deal

Java unresolved JPanel variable?

java eclipse

Why can't aniPanel be resolved to a variable. I've tried