Home » SQL & PL/SQL » SQL & PL/SQL » Inserting Single Quotes (Oracle Database 11g)
|
|
|
|
| Re: Inserting Single Quotes [message #571957 is a reply to message #571956] |
Tue, 04 December 2012 17:32   |
 |
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   |
 |
Michel Cadot
Messages: 54698 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   |
_jum
Messages: 459 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 #572327 is a reply to message #571996] |
Mon, 10 December 2012 14:11  |
Bill B
Messages: 997 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'
|
|
|
|
Goto Forum:
Current Time: Wed Jun 19 06:00:36 CDT 2013
Total time taken to generate the page: 0.08029 seconds
|