CODEWITHSUNDEEP
sql-serverreplacefind
Chantelle Brink
Chantelle Brink

Reputation: 93

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?

Upvotes: 3

Views: 2969

Answers (4)

sagi
sagi

Reputation: 40491

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.

Upvotes: 1

Matt Gibson
Matt Gibson

Reputation: 38238

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

Upvotes: 2

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

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;

Upvotes: 2

A_Sk
A_Sk

Reputation: 4630

Try

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

Upvotes: 0

Related Questions