OraFAQ Forum: SQL & PL/SQL » Creating SQL String

Home » SQL & PL/SQL » SQL & PL/SQL » Creating SQL String (Oracle 11g)

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

Creating SQL String [message #639890]

Mon, 20 July 2015 03:43

Xandot
Messages: 235
Registered: January 2014
Location: India

Senior Member

Hi All,

I have one string like- 'One:Two:Three'. I want to create string like- 'One','Two','Three'.

Input-
'One:Two:Three'

Expected output like-
'One','Two','Three'

Please help me out !

Report message to a moderator

Re: Creating SQL String [message #639894 is a reply to message #639890]

Mon, 20 July 2015 04:33

anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India

Senior Member

Use quote delimiter and replace

Below URL is for your reference
http://psoug.org/reference/string_func.html

e.g. SELECT q'['One:Two:Three']' STR FROM DUAL

Thanks,
Anil MK

Report message to a moderator

Re: Creating SQL String [message #639896 is a reply to message #639890]

Mon, 20 July 2015 04:41

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

Senior Member
Account Moderator

SQL> select substr(regexp_replace(replace('One:Two:Three',':','::'),
  2                               ':?([^:]+):?',',''\1'''),
  3                2) res
  4  from dual;
RES
-------------------
'One','Two','Three'

Report message to a moderator

Re: Creating SQL String [message #639901 is a reply to message #639896]

Mon, 20 July 2015 04:46

Xandot
Messages: 235
Registered: January 2014
Location: India

Senior Member

Thanks a lot Michel

Report message to a moderator

Re: Creating SQL String [message #639951 is a reply to message #639890]

Mon, 20 July 2015 10:31

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

Senior Member

Xandot wrote on Mon, 20 July 2015 14:13

I have one string like- 'One:Two:Three'. I want to create string like- 'One','Two','Three'.

That should be a simple use of REPLACE and concatenate with single-quotation marks.

SQL> WITH DATA(str) AS(
  2  SELECT 'One:Two:Three' FROM dual
  3  )
  4  SELECT str, ''''||REPLACE(str, ':', ''',''')||'''' new_str FROM DATA;

STR           NEW_STR
------------- -----------------------------------------
One:Two:Three 'One','Two','Three'

SQL>

Report message to a moderator

Re: Creating SQL String [message #639956 is a reply to message #639951]

Mon, 20 July 2015 11:05

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

Senior Member
Account Moderator

Yes I wanted to give another solution than the one suggested by Anil.

Report message to a moderator

Re: Creating SQL String [message #639958 is a reply to message #639894]

Mon, 20 July 2015 11:38

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

Senior Member

anil_mk wrote on Mon, 20 July 2015 15:03

e.g. SELECT q'['One:Two:Three']' STR FROM DUAL

It seems plain and simple with hard-coded value. Sometimes it is easier to use the concatenation operator than quoting-string literal when the value is actually coming from a column/variable.

Hard-coded value:

SQL> SELECT REPLACE(q'['One:Two:Three']',':', q'[',']') str FROM DUAL;

STR
-------------------
'One','Two','Three'

SQL>

Column reference:

SQL> WITH DATA(str) AS(
  2  SELECT 'One:Two:Three' FROM dual
  3  )
  4  SELECT str, REPLACE(q'[']'||str, ':', q'[',']')||q'[']' new_str FROM DATA;

STR           NEW_STR
------------- -------------------------------------------
One:Two:Three 'One','Two','Three'

SQL> WITH DATA(str) AS(
  2  SELECT 'One:Two:Three' FROM dual
  3  )
  4  SELECT str, ''''||REPLACE(str, ':', ''',''')||'''' new_str FROM DATA;

STR           NEW_STR
------------- -----------------------------------------
One:Two:Three 'One','Two','Three'

SQL>

Report message to a moderator