CODEWITHSUNDEEP
sqlsql-server
Linto
Linto

Reputation: 1282

Update query in sql

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

Upvotes: 2

Views: 226

Answers (5)

Chris Rogers
Chris Rogers

Reputation: 1863

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

Upvotes: 2

Thomas
Thomas

Reputation: 64674

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

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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

Upvotes: 0

Trey Carroll
Trey Carroll

Reputation: 2382

Use the Replace function to strip out the double quotes.

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

Upvotes: 3

Gustav Larsson
Gustav Larsson

Reputation: 8497

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);

Upvotes: 0

Related Questions