Results 1 to 2 of 2

Thread: Regexp Replace

  1. #1
    Join Date
    Dec 2008
    Posts
    135

    Unanswered: Regexp Replace

    [/code]
    Hi,

    i tried to replace the numeric amount to x in a string using regexp_replace.

    but the amount values are having comma then the total value is not replacing with X.

    sample data:
    Total Amount: 210,000.00 Date:01-JUL-2019 valid Date:31-DEC-2023 Unit Amount: 10,000.00
    %Due:33.3333 Date:01-JUL-10 Amount Due: 64,666.60 System :Y Comment:testing

    required output:
    Total Amount: x Date:01-JUL-2019 valid Date:31-DEC-2023 Unit Amount: x
    %Due:x Date:01-JUL-10 Amount Due: x System :Y Comment:testing

    i had tried like this:
    select distinct columnname,
    REGEXP_REPLACE(columnname,
    '*[^[:digit:]][[:digit:]]*\.[[:digit:]]{2}',
    'XXXX')"REGEXP_REPLACE"
    from tablename

    output getting from the query is
    Total Amount: 210x Date:01-JUL-2019 valid Date:31-DEC-2023 Unit Amount: 10x
    %Due:x33 Date:01-JUL-10 Amount Due: 64x System :Y Comment:testing
    [/code]

  2. #2
    Join Date
    May 2006
    Posts
    132
    Code:
    SQL> select * from amount;
    
    A1
    ----------------------------------------------------------------------
    Total Amount: 210,000.00 Date:01-JUL-2019 valid Date:31-DEC-2023 Unit
    Amount: 10,000.00 %Due:33.3333 Date:01-JUL-10 Amount Due: 64,666.60 Sy
    stem :Y Comment:testing
    
    
    SQL> select regexp_replace(a1, '\d+(,|\.)\d+\.*\d*', 'x') a1 from amount;
    
    A1
    ----------------------------------------------------------------------
    Total Amount: x Date:01-JUL-2019 valid Date:31-DEC-2023 Unit Amount: x
     %Due:x Date:01-JUL-10 Amount Due: x System :Y Comment:testing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •