Update query in sql

Question

I have table Table1. With fields f1, f2

The table values are like that (values contains quotes)

 "Hello1", "ok1"
 "Hello2", "ok2"

I need a update query so that it will remove the quotes from the values and it should look like

 Hello1, ok1
 Hello2, ok2

There are a lot of SQL databases.. What database engine are you using? i.e. MySQL, PostgreSQL, Oracle, etc — yan
– yan, Commented May 3, 2011 at 22:01
@user310850 - Do we assume all of rows contain surrounding quotes? Or only some? Also, what version of SQL you're using? — Chris Rogers
– Chris Rogers, Commented May 3, 2011 at 22:01
@user310850 - What database product and version? I.e., MySQL, SQL Server, Oracle? — Thomas
– Thomas, Commented May 3, 2011 at 22:01
Just to clarify : do you want surrounding quotes to be removed? or ALL quotes? — Chris Rogers
– Chris Rogers, Commented May 3, 2011 at 22:04

Trey Carroll · Accepted Answer · 2011-05-03 22:02:38Z

3

Use the Replace function to strip out the double quotes.

UPDATE Table1 set f1 = REPLACE(f1,'"',''), f2 = REPLACE(f2,'"','');

answered May 3, 2011 at 22:02

Trey Carroll

2,3824 gold badges23 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Linto Over a year ago

Can you write the full query to get tthe out put that i required please

Trey Carroll Over a year ago

After you run the update it's just a matter of Selecting the columns: SELECT f1, f2 FROM Table1

Conrad Frix Over a year ago

@user310850 that's the whole thing unless you want to add a where

Chris Rogers · Accepted Answer · 2011-05-03 22:11:49Z

2

If you want to remove only the surrounding quotes use :

UPDATE Table1 SET
f1 = SUBSTRING(f1, 2, LEN(f1)-2),
f2 = SUBSTRING(f2, 2, LEN(f2)-2)

If you want to remove all quotes (including middle ones) use :

UPDATE Table1 set f1 = REPLACE(f1,'"',''), f2 = REPLACE(f2,'"','')

Then to get the final output, you can use :

SELECT * FROM Table1

answered May 3, 2011 at 22:11

Chris Rogers

1,8731 gold badge16 silver badges18 bronze badges

Comments

Gustav Larsson · Accepted Answer · 2011-05-03 22:02:16Z

0

I think this might depend on the variety of SQL, but in MySQL you can do:

UPDATE Table1 SET f1 = TRIM(BOTH '"' FROM f1), f2 = TRIM(BOTH '"' FROM f2);

answered May 3, 2011 at 22:02

Gustav Larsson

8,5173 gold badges33 silver badges52 bronze badges

Comments

Mikael Eriksson · Accepted Answer · 2011-05-03 22:07:06Z

0

You can use substring and len to get only a part of the string.

Use this update statement to change the content of your table

update Table1 set
  f1 = substring(f1, 2, len(f1)-2),
  f2 = substring(f2, 2, len(f2)-2)

If you only want to change the output of a query you can do this

select
  substring(f1, 2, len(f1)-2),
  substring(f2, 2, len(f2)-2)
from Table1

answered May 3, 2011 at 22:07

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Comments

Thomas · Accepted Answer · 2011-05-03 22:11:47Z

0

Update Table1
Set f1 = Case
            When f1 Like '"%' And f1 Like '%"' Then Substring(f1,2,Len(f1)-2)
            When f1 Like '"%' Then Substring(f1,1,Len(f1)-1)
            When f1 Like '%"' Then Substring(f1,2,Len(f1)-1)
            Else f1
            End
    , f2 = Case
            When f2 Like '"%' And f2 Like '%"' Then Substring(f2,2,Len(f2)-2)
            When f2 Like '"%' Then Substring(f2,1,Len(f2)-1)
            When f2 Like '%"' Then Substring(f2,2,Len(f2)-1)
            Else f2
            End

edited May 3, 2011 at 22:11

answered May 3, 2011 at 22:00

Thomas

64.8k12 gold badges98 silver badges142 bronze badges

Collectives™ on Stack Overflow

Update query in sql

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related