CODEWITHSUNDEEP
regexoracle-databaseoracle10g
P.Muthu Manickam
P.Muthu Manickam

Reputation: 1

Regular expression in Oracle to Filter particular charecters only

I have a scenario

Case 1: "NO 41 ABC STREET"
Case 2: "42 XYZ STREET"

For almost 100 000 data in my table.

I want a regexp that

Upvotes: 0

Views: 35

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

You have provided only 2 scenarios of your data in the table. Assuming that you only want to replace the characters in a column which starts with a "NO" followed by digit and then space before some other characters, you could use this.

SQL Fiddle

Query:

select s,REGEXP_REPLACE(s,'^NO +\d+ +') as r FROM data

Results:

|                S |             R |
|------------------|---------------|
| NO 41 ABC STREET |    ABC STREET |
|    42 XYZ STREET | 42 XYZ STREET |

If you have more complex data to be filtered, please edit your question and describe it clearly.

Upvotes: 1

blhsing
blhsing

Reputation: 106543

regexp_replace('NO 41 ABC STREET', 'NO [0-9]+ |([0-9]+)', '\1') outputs ABC STREET. regexp_replace('42 XYZ STREET', 'NO [0-9]+ |([0-9]+)', '\1') outputs 42 XYZ STREET.

Upvotes: 1

Related Questions