CODEWITHSUNDEEP
sqlregexoracle-databaseregexp-replace
nvacin
nvacin

Reputation: 23

Oracle Regexp_replace string

Let's say I have the following string:

notNull(devhx_8_other2_name_2)  AND   notNull(devhx_8_other2_amt)

How can I use regexp_replace to change it to:

(devhx_8_other2_name_2) is not null AND (devhx_8_other2_amt) is not null

Upvotes: 1

Views: 947

Answers (4)

MT0
MT0

Reputation: 167774

You can use the pattern:

  • notNull - match the string
  • ( - start a capture group
  • \(.+?\) - match an opening bracket then one-or-more characters but as few as possible until it matches a closing bracket
  • ) - end of the capture group.

And then replace it with \1 is not null which will substitute \1 for the value matched in the first capture group. Like this: 

SELECT REGEXP_REPLACE(
         your_column,
         'notNull(\(.+?\))',
         '\1 is not null'
       )
FROM   your_table

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Use

regexp_replace(col, 'notNull(\([^)]+\))', '\1 is not null', 1, 0)

This looks for 'notNull' followed immediately by an opening parenthesis, other characters and a closing parenthesis. It replaces this with the string including the parentheses, but without 'notNull' and appends 'is not null'.

Upvotes: 2

Aleksej
Aleksej

Reputation: 22949

Assuming that your strings are always in the format you showed, you don't need regular expressions:

replace( replace( yourString, ')', ') is not null '), 'notNull', '')

Upvotes: 1

Rupert
Rupert

Reputation: 150

Use the following regexp_replace function:

regexp_replace(regexp_replace(string_name,"notNull",""),"')","') is not null")

Here I replace the 'notNull' with a non space i.e. '' and then replace the closing bracket i.e. ')' with a closing bracket, a space and the text 'is not null'.

Upvotes: -1

Related Questions