Home » SQL & PL/SQL » SQL & PL/SQL » Query using REGEXP funtion (10.2.0.3.0)
Query using REGEXP funtion [message #408856] Thu, 18 June 2009 01:45 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Query using REGEXP funtion [message #408870 is a reply to message #408863] Thu, 18 June 2009 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Did you execute the code you posted?
Anyway, this does not reply to the main point:
Quote:
You MUST explain the rules with words.

Regards
Michel
Re: Query using REGEXP funtion [message #408883 is a reply to message #408857] Thu, 18 June 2009 02:54 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Michel Cadot wrote on Thu, 18 June 2009 08:52
You MUST explain the rules with words.
Strong language, but I catch your drift.

@user71408: if you want people like Michel to help you out by rewriting the function, it is important that he understands what your function is supposed to do. Therefore, he stressed the fact that the explanation is really important. Wink


Could it be as simple as this?
select fn_format_phnum(replace(<yourstring>,'-','||')) z
from dual
/


MHE
Previous Topic: Trigger to update table in other schema
Next Topic: Getting date problem
Goto Forum:
  


Current Time: Tue Feb 11 13:41:55 CST 2025