Home » SQL & PL/SQL » SQL & PL/SQL » Creating String (Oracle 11g)
| Creating String [message #640596] |
Fri, 31 July 2015 08:25  |
Xandot
Messages: 235
Registered: January 2014
Location: India
|
Senior Member |
|
|
Hi All,
I have one query and I want to modify string like below output:
I/P Query:
select 'Production No: PO_P12334, PO_P23234, 23232:2, 23323:4' str from dual
Output Like:
Str
------
PO_P12334;PO_P23234;23232:2;23323:4;
Thanks,
Xandot
[MERGED by LF]
[Updated on: Fri, 21 August 2015 01:02] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Creating String [message #640604 is a reply to message #640602] |
Fri, 31 July 2015 08:45  |
Xandot
Messages: 235
Registered: January 2014
Location: India
|
Senior Member |
|
|
String : 'Production No: PO_P12334, PO_P23234, 23232:2, 23323:4'
Rules : 1- No Spaces between comma
2- comma should be replace by ';'
3- final output should be like : PO_P12334;PO_P23234;23232:2;23323:4;
|
|
|
|
|
|
| Re: Creating String [message #640606 is a reply to message #640605] |
Fri, 31 July 2015 09:44 |
Xandot
Messages: 235
Registered: January 2014
Location: India
|
Senior Member |
|
|
I'm trying but spaces are still there:
select replace(substr('Production No: PO_P12334, PO_P23234, 23232:2, 23323:4', instr('Production No: PO_P12334, PO_P23234, 23232:2, 23323:4',':')+2),',',';')||';' from dual;
Please help me out.
|
|
|
|
| Re: Creating String [message #640607 is a reply to message #640606] |
Fri, 31 July 2015 09:50 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
Xandot wrote on Fri, 31 July 2015 07:44I'm trying but spaces are still there:
select replace(substr('Production No: PO_P12334, PO_P23234, 23232:2, 23323:4', instr('Production No: PO_P12334, PO_P23234, 23232:2, 23323:4',':')+2),',',';')||';' from dual;
Please help me out.
when all else fails, Read The Fine Manual
https://docs.oracle.com/database/121/SQLRF/functions166.htm#sthref1829
|
|
|
|
|
|
| Re: Creating String [message #640612 is a reply to message #640608] |
Fri, 31 July 2015 10:44 |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Here's the first step to get the result which is not in your rules:
SQL> with data as (select 'Production No: PO_P12334, PO_P23234, 23232:2, 23323:4' str from dual)
2 select substr(str,instr(str,' ',1,2)+1) from data;
SUBSTR(STR,INSTR(STR,'',1,2)+1)
--------------------------------------
PO_P12334, PO_P23234, 23232:2, 23323:4
[Updated on: Fri, 31 July 2015 10:45] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Creating String [message #641652 is a reply to message #641637] |
Wed, 19 August 2015 14:57 |
 |
shahbazali.001
Messages: 1
Registered: December 2014
Location: Pakistan
|
Junior Member |
|
|
i think it will work.......
select replace(substr('Production No: PO_P12334, PO_P23234, 23232:2, 23323:4',
instr('Production No: PO_P12334, PO_P23234, 23232:2, 23323:4',':')+2),', ',';') from dual
|
|
|
|
|
|
| Creating String [message #641672 is a reply to message #640596] |
Thu, 20 August 2015 03:21 |
Xandot
Messages: 235
Registered: January 2014
Location: India
|
Senior Member |
|
|
Hi All,
I have string like:
select 'PB_T12345,PB_T67890,123434:2,657657:3,PB_T23233' from dual;
I want output like:
String should not contain any value which has 'PB_T', Please help me out.
Thanks,
Xandot
|
|
|
|
|
|
| Re: Creating String [message #641677 is a reply to message #641672] |
Thu, 20 August 2015 03:40 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
I think you should try to first learn from so many solutions already provided to you. All your questions are similar to each other.
SQL> WITH t(str) AS(
2 SELECT 'PB_T12345,PB_T67890,123434:2,657657:3,PB_T23233' FROM dual
3 )
4 SELECT listagg(str, ',') within GROUP(
5 ORDER BY id) str
6 FROM
7 (SELECT level id,
8 trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
9 FROM t
10 CONNECT BY LEVEL <= regexp_count(str, ',')+1
11 )
12 WHERE instr(str, 'PB_T') = 0;
STR
-----------------------------------------------------------------------
123434:2,657657:3
SQL> |
|
|
|
| Re: Creating String [message #641679 is a reply to message #641677] |
Thu, 20 August 2015 03:43 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Lalit Kumar B wrote on Thu, 20 August 2015 09:40I think you should try to first learn from so many solutions already provided to you.
You rather undermined that sentence by posting the code don't you think?
|
|
|
|
| Re: Creating String [message #641680 is a reply to message #641679] |
Thu, 20 August 2015 03:47 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
|
Hmm...I should have seen all his previous questions, I just saw those which came up today. Now I see almost all his previous questions revolving around similar topic.
|
|
|
|
| Re: Creating String [message #641683 is a reply to message #641680] |
Thu, 20 August 2015 04:03 |
Xandot
Messages: 235
Registered: January 2014
Location: India
|
Senior Member |
|
|
Thanks Lalit.. I apologies for repetitive threads..
select LISTAGG(cp, ',')WITHIN GROUP (ORDER BY NULL) from
(select regexp_substr('PB_T12345,PB_T67890,123434:2,657657:3,PB_T23233','[^,]+', 1, level) as cp from dual
connect by regexp_substr('PB_T12345,PB_T67890,123434:2,657657:3,PB_T23233', '[^,]+', 1, level) is not null)
where instr(cp, 'PB_T')=0; |
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 05:01:55 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
