Picking up Number From a String Using Regular Expressions - Oracle Sql

 In Oracle SQL, you can use regular expressions to extract exactly the 5-digit number from a string like "Multiple emp ID in this text 1.) 7369 2.> 7521 3.] 7782 4.} 7934".

Here’s a one-liner using REGEXP_SUBSTR:

Select REGEXP_SUBSTR('Multiple emp ID in this text 1.) 7369 2.> 7521 3.] 7782 4.} 7934',

                     '\d{4}') As Extracted_Number

  From dual;

Explanation:

• \d{4} matches exactly 4 consecutive digits

• REGEXP_SUBSTR returns the matching substring (i.e., 7369)

---

Bonus:

If the string might contain more than one 4-digit number and you want all of them, you can loop through them using CONNECT BY like:

Select REGEXP_SUBSTR('Multiple emp ID in this text 1.) 7369 2.> 7521 3.] 7782 4.} 7934',

                     '\d{4}',

                     1,

                     Level) As match

  From dual

Connect By REGEXP_SUBSTR('Multiple emp ID in this text 1.) 7369 2.> 7521 3.] 7782 4.} 7934',

                         '\d{4}',

                         1,

                         Level) Is Not Null;

Or use above as in-line:

Select *

  From Emp a

 Where a.Empno In (Select REGEXP_SUBSTR('Multiple emp ID in this text 1.) 7369 2.> 7521 3.] 7782 4.} 7934',

                                        '\d{4}',

                                        1,

                                        Level) As match

                     From dual

                   Connect By REGEXP_SUBSTR('Multiple emp ID in this text 1.) 7369 2.> 7521 3.] 7782 4.} 7934',

                                            '\d{4}',

                                            1,

                                            Level) Is Not Null);