0

While converting Oracle to Postgresql I came to know the following Oracle query need to be converted in Postgres.

Oracle Query: Find pattern and replace with null

select regexp_replace('1', '[^0-9]', null) from dual;
select regexp_replace('a', '[^0-9]', null) from dual;
select regexp_replace('1a1', '[^0-9]', null) from dual;

My try:

As per the postgres document we need to use REGEXP_REPLACE with [[:alpha:]] pattern.

But the statement is replacing with empty string if match found. I'm looking for null instead.

PostgreSQL Query:

select REGEXP_REPLACE('1','[[:alpha:]]','','g') --Correct
select REGEXP_REPLACE('a','[[:alpha:]]','','g') --Wrong: output should be NULL
select REGEXP_REPLACE('1a1','[[:alpha:]]','','g') --Correct
select REGEXP_REPLACE(' ','[[:alpha:]]','','g') --Wrong: output should be NULL

Definitely we can use case statement like following but I want the solution in single line without using case condition.

SELECT case when REGEXP_REPLACE('1a','[[:alpha:]]','','g') = '' 
       then 
           null 
       else 
           REGEXP_REPLACE('1a','[[:alpha:]]','','g') 
       end;
MAK
  • 6,824
  • 25
  • 74
  • 131
  • In Postgres (and essentially every other DBMS) an empty string (`''`) is something different than a `null` value. So if you replace all characters in a string, the result is an empty string, not a null value. If you want to treat an empty string as null use `nullif(..., '')` –  Jul 28 '22 at 12:22
  • @a_horse_with_no_name, I need to convert this column into `::bigint` post `regexp`. – MAK Jul 28 '22 at 12:24
  • 1
    Then use `nullif()` –  Jul 28 '22 at 12:35
  • 1
    Something like this? select NULLIF(REGEXP_REPLACE('a','[[:alpha:]]','','g'), '')::BIGINT; – user_0 Jul 28 '22 at 12:35

0 Answers0