Home » SQL & PL/SQL » SQL & PL/SQL » Urgent Help Required!! PL/SQL Table Doubt
|
|
|
|
Re: Urgent Help Required!! PL/SQL Table Doubt [message #306774 is a reply to message #306762] |
Sun, 16 March 2008 13:36   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Instead of using such a PL/SQL "monster" code, how about something simpler? It, though, requires PL/SQL "background" function called CONCAT_ALL (check Barbara Boehmer's post here and follow link she provided).
Here's an example (CONCAT_ALL has already been created):
SELECT
concat_all(concat_expr(DECODE(odd_digit , 0, NULL, odd_digit) , '')) odd,
concat_all(concat_expr(DECODE(even_digit, 0, NULL, even_digit), '')) even
FROM
(SELECT SUBSTR('1234567891', LEVEL, 1) * SUBSTR('101010101010101', LEVEL, 1) odd_digit,
SUBSTR('1234567891', LEVEL, 1) * SUBSTR('010101010101010', LEVEL, 1) even_digit
FROM dual
CONNECT BY LEVEL <= LENGTH('1234567891')
);
ODD EVEN
---------- ----------
13579 24681
Shortly: multiply telephone number with a binary mask (1010... - I presumed that telephone numbers aren't longer than 15 digits) which starts with 1 or 0, depending on whether you want odd or even part of it. Subquery will return rows and we'll use CONCAT_ALL against this result in order to produce a string.
|
|
|
|
|
Re: Urgent Help Required!! PL/SQL Table Doubt [message #307403 is a reply to message #307363] |
Tue, 18 March 2008 16:32   |
mnitu
Messages: 159 Registered: February 2008 Location: Reims
|
Senior Member |
|
|
Sorry, I've misted it.
Bellow is basically the same idea used by Litlefoot but it does not need CONCAT_ALL
Select replace(replace(odd,'/',''),'0','') odd,
replace(replace(even,'/',''),'0','') even
from (
Select level lv,
sys_connect_by_path(decode(mod(level,2), 1,SubStr('1234567891',level,1),0),'/') odd,
sys_connect_by_path(decode(mod(level,2), 0,SubStr('1234567891',level,1),0),'/') even
from dual
connect by level <= length('1234567891')
)
Where lv = length('1234567891')
ODD EVEN
---------- ----------
13579 24681
|
|
|
Re: Urgent Help Required!! PL/SQL Table Doubt [message #307579 is a reply to message #306718] |
Wed, 19 March 2008 03:38   |
mnitu
Messages: 159 Registered: February 2008 Location: Reims
|
Senior Member |
|
|
Supposing Oracle 10g and fixed length character strings there is a very nice solution based on regular expression
Select regexp_replace('1234567891','([0-9]).([0-9]).([0-9]).([0-9]).([0-9]).','\1\2\3\4\5') odd,
regexp_replace('1234567891','.([0-9]).([0-9]).([0-9]).([0-9]).([0-9])','\1\2\3\4\5') even
From dual
|
|
|
Re: Urgent Help Required!! PL/SQL Table Doubt [message #307592 is a reply to message #306718] |
Wed, 19 March 2008 04:10  |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
Thank for the solutions,These are really nice,precise piece of logic,I need to understand the working of it as well.
About my code in the procedure i can now do it from that as well,as i needed to initialise the variables,bcoz when it appends with the initial substr function it founds null,so i initialise it to 0.
and working.
|
|
|
Goto Forum:
Current Time: Mon Feb 17 23:55:47 CST 2025
|