Query using REGEXP funtion [message #408856] |
Thu, 18 June 2009 01:45  |
user71408
Messages: 585 Registered: November 2007 Location: NE
|
Senior Member |
|
|
Hi All,
I have column called NUMBERS in ABC table.
I have data as follows.
ROW NUMBERS
--------------
1 8976558500-8976558505||8976558550||8976558555
2 8976558501-8976558506||8976558551||8976558556
...
...
More than 100000 records I have
Required O/P is :
ROW NUMBERS
--------------
1 076/55.85.00||076/55.85.05||076/55.85.50||076/55.85.55
2 076/55.85.01||076/55.85.06||076/55.85.51||076/55.85.56
....
...
But I am getting as follows
ROW NUMBERS
--------------
1 076/55.85.00||-07/65.58.50||076/55.85.50||076/55.85.55
2 076/55.85.01||-07.65.58.50||076/55.85.51||076/55.85.56
...
..
I have written the following function.. can you please look into this and correct it.
FUNCTION fn_format_phnum (msisdn_in VARCHAR2)
RETURN VARCHAR
IS
msisdn_out VARCHAR2 (2000);
single_phone_number VARCHAR2 (2000);
WORK VARCHAR2 (2000);
POSITION NUMBER;
BEGIN
WORK := msisdn_in;
LOOP
POSITION := INSTR (WORK, '||', 1);
IF POSITION = 0
THEN
single_phone_number := WORK;
WORK := '';
ELSE
single_phone_number := SUBSTR (WORK, 1, POSITION - 1);
WORK := SUBSTR (WORK, POSITION + 2);
END IF;
msisdn_out:=msisdn_out
|| '||'
|| RTRIM (REGEXP_REPLACE (REPLACE (REPLACE (single_phone_number,
'32',
0
),
'||',
''
),
'(...)(..)(..)(..)',
'\1/\2.\3.\4||'
),
'||'
);
EXIT WHEN WORK IS NULL;
END LOOP;
RETURN LTRIM (msisdn_out, '|');
END;
Thank you,
SNN
|
|
|
Re: Query using REGEXP funtion [message #408857 is a reply to message #408856] |
Thu, 18 June 2009 01:52   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
I'm not getting what you get:
SQL> select fn_format_phnum ('8976558500-8976558505||8976558550||8976558555') from dual;
FN_FORMAT_PHNUM('8976558500-8976558505||8976558550||8976558555')
----------------------------------------------------------------------------------------
897/65.58.50||0-8/97.65.58||505||897/65.58.55||0||897/65.58.55||5
You MUST explain the rules with words.
Regards
Michel
[Updated on: Thu, 18 June 2009 01:53] Report message to a moderator
|
|
|
Re: Query using REGEXP funtion [message #408863 is a reply to message #408857] |
Thu, 18 June 2009 02:06   |
user71408
Messages: 585 Registered: November 2007 Location: NE
|
Senior Member |
|
|
Hi ,
I am getting
select fn_format_phnum ('8976558500-8976558505||8976558550||8976558555') from dual;
FN_FORMAT_PHNUM('8976558500-8976558505||8976558550||8976558555')
----------------------------------------------------------------
076/55.85.00||[B]-07/65.58.50[/B]||076/55.85.05||076/55.85.55
here in block leeters num i need to display it as 076/55.85.05 but I am getting '-' and 6 is missing in the format.
i need the o/p as
076/55.85.00||[B]076/55.85.50[/B]||076/55.85.05||076/55.85.55
Thank you,
SNN
|
|
|
|
|