Search code examples
sql-serverreplacefind

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''

Required 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?

Solution

  • 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.