OraFAQ Forum: SQL & PL/SQL » how to user regexp to put single quotes

Home » SQL & PL/SQL » SQL & PL/SQL » how to user regexp to put single quotes (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to user regexp to put single quotes [message #635637]

Fri, 03 April 2015 15:10

gentleman777us
Messages: 122
Registered: April 2005

Senior Member

Hi,

How can I return a string embeded in single quotes.

ex: select 'hello1, hello2' as val from dual;

would return

val
---
hello1, hello2

I want it to return

val
------
'hello1', 'hello2'

Please note the single quotes around each value.

Any help is greately appreciated.

Thanks

Report message to a moderator

Re: how to user regexp to put single quotes [message #635638 is a reply to message #635637]

Fri, 03 April 2015 15:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> select '''hello1'',''hello2''' from dual;

'''HELLO1'',''HEL
-----------------
'hello1','hello2'

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: how to user regexp to put single quotes [message #635639 is a reply to message #635637]

Fri, 03 April 2015 15:20

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What does regexp have to do with this?
And once more, FORMAT YOUR POSTS.

Report message to a moderator

Re: how to user regexp to put single quotes [message #635643 is a reply to message #635637]

Sat, 04 April 2015 01:17

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

You could use the quoting string literals technique which was introduced in 10g.

SQL> SELECT q'['hello1', 'hello2']' str FROM DUAL;

STR
------------------
'hello1', 'hello2'

SQL>

But, as your question says "to put single quotes"... you could add the quotes in the beginning and end, and replace the comma and space in between the words with single quotes and comma in between.

SQL> WITH DATA AS(
  2  SELECT 'hello1, hello2' str FROM DUAL
  3  )
  4  SELECT q'[']'||replace(str, ', ', q'[',']')||q'[']' str FROM DATA;

STR
-----------------
'hello1','hello2'

SQL>

Regards,
Lalit

Report message to a moderator

Previous Topic:	Need help in tablespace
Next Topic:	Package Initialization block for performance tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 15:45:34 CDT 2024