Home » SQL & PL/SQL » SQL & PL/SQL » Inserting Single Quotes (Oracle Database 11g)
Inserting Single Quotes [message #571955] Tue, 04 December 2012 17:19 Go to next message
Voltage
Messages: 2
Registered: December 2012
Junior Member
I have a string:
'VOLT,AGE'

How can I convert this string to:
'VOLT','AGE'
using REGEXP_REPLACE

I am having trouble escaping the single quotes in my query Sad

[Updated on: Tue, 04 December 2012 17:22]

Report message to a moderator

Re: Inserting Single Quotes [message #571956 is a reply to message #571955] Tue, 04 December 2012 17:25 Go to previous messageGo to next message
BlackSwan
Messages: 22500
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Inserting Single Quotes [message #571957 is a reply to message #571956] Tue, 04 December 2012 17:32 Go to previous messageGo to next message
Voltage
Messages: 2
Registered: December 2012
Junior Member
Here is what I tried:-
SQL> select regexp_replace('AB,CD','(..),',q'['\1',]') from dual;
'AB',CD

SQL>select regexp_replace('AB,CD,DE','(..),',q'['\1',]') from dual;
'AB','CD',DE

But in both cases the single quote is missing in the last pair, can somebody help me fix the regex and replacement rules?
EDIT: Never mind, got the solution.

[Updated on: Tue, 04 December 2012 23:32]

Report message to a moderator

Re: Inserting Single Quotes [message #571976 is a reply to message #571957] Wed, 05 December 2012 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 58568
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why no just using the good old REPLACE?
SQL> with data as (select q'['VOLT,AGE']' v from dual)
  2  select v, replace(v, ',', q'[',']') v2 from data;
V          V2
---------- ------------------------------
'VOLT,AGE' 'VOLT','AGE'

Regards
Michel
Re: Inserting Single Quotes [message #571994 is a reply to message #571976] Wed, 05 December 2012 04:53 Go to previous messageGo to next message
_jum
Messages: 486
Registered: February 2008
Senior Member
Of course you can use regexp too (extending @Michels solution):
WITH data AS 
  (SELECT q'['VOLT,AGE']' v FROM DUAL)
SELECT v,
       REPLACE (v, ',', q'[',']')              v2,
       REGEXP_REPLACE (v, '(.),', q'[\1',']') r1
  FROM data;

v               v2               r1
----------------------------------------------
'VOLT,AGE'	'VOLT','AGE'	'VOLT','AGE'

Changed (.*) to (.)

[Updated on: Wed, 05 December 2012 04:56]

Report message to a moderator

Re: Inserting Single Quotes [message #571996 is a reply to message #571994] Wed, 05 December 2012 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 58568
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why using regexp_replace which is expensive when you have the cheap solution with REPLACE?

Regards
Michel
Re: Inserting Single Quotes [message #572327 is a reply to message #571996] Mon, 10 December 2012 14:11 Go to previous message
Bill B
Messages: 1068
Registered: December 2004
Senior Member
You can also do it by not using the escape

with data as (select q'['VOLT,AGE']' v from dual)
select v, replace(v, ',', ''',''') v2 from data;

V          V2
---------- ------------------------------
'VOLT,AGE' 'VOLT','AGE'
Previous Topic: Gathering Stats
Next Topic: CLOB TO BLOB UPDATION
Goto Forum:
  


Current Time: Fri Jul 25 13:52:37 CDT 2014

Total time taken to generate the page: 0.10233 seconds