how to replace quotes in db2 sql

t9119 · #1 (**permalink**) 11-22-09, 20:55

the replace function cannot be used to replace single or double quotes. cna you let me know if there is any way to replace quotes with other character. thanks in advance

Marcus_A · #2 (**permalink**) 11-22-09, 21:09

Find out the ascii hex value, and then replace it with another hex value or space. For example X'22' is a double-quote.
Ascii Table - ASCII character codes and html, octal, hex and decimal chart conversion

DB2Plus · #3 (**permalink**) 11-22-09, 23:28

you have to use double quotes not a single...

Kara

stolze · #4 (**permalink**) 11-23-09, 13:31

You can replace single or double quotes just fine. The only thing to remember is to use proper escaping. Escaping in SQL is to double the escaped character, i.e. a single quote inside a string is ''.

t9119 · #5 (**permalink**) 11-23-09, 17:34

can i use the replace function in db2 to do that

stolze · #6 (**permalink**) 11-23-09, 18:20

If this was a question, the answer is: yes, you can do that.

t9119 · #7 (**permalink**) 11-24-09, 17:41

what about using regular expression in replace or translate function like can i use something like ^[A-Za-z0-9] if i want to replace anything other than alpha numeric.

Thanks

stolze · #8 (**permalink**) 11-25-09, 09:03

TRANSLATE and REPLACE don't work with regular expressions. But you can roll your own function if you need to: Bringing the Power of Regular Expression Matching to SQL

dr_te_z · #9 (**permalink**) 02-15-12, 08:49

Google found this old thread but the answer is not here. So here you go:

Code:

db2 "values replace(varchar('hoi''s'),x'27',space(1))"

The single quote will be replaced by a space
"hoi's" := "hoi s"

Marcus_A · #10 (**permalink**) 02-15-12, 08:55

Quote:

Originally Posted by dr_te_z

Google found this old thread but the answer is not here. So here you go:

Code:

db2 "values replace(varchar('hoi''s'),x'27',space(1))"

The single quote will be replaced by a space
"hoi's" := "hoi s"

How about this as I suggested over two years ago:

Code:

db2 "values replace(varchar('hoi''s'),x'27',x'20')"

1
-----
hoi s

  1 record(s) selected.

stolze · #11 (**permalink**) 02-15-12, 14:36

Or if someone doesn't like to use ASCII code points:

Code:

replace(varchar('hoi''s'), '''', ' ')

Internet Services

Web Development

Digital Marketing

Consumer Tech