CODEWITHSUNDEEP
regexoracle-databaseoracle-sqldeveloper
RobbeVL
RobbeVL

Reputation: 83

Extract Numbers from String - Custom

I'd like to extract "Most" numbers from a string and Add "JW" at the end. My values look like:

RFID_DP_IDS339020JW3_IDMsg - Result = 339020JW
RFID_DP_IDSA72130JW_IDMsg --> 72130JW
RFID_DP_IDS337310JW1_IDMsg --> 337310JW

Basically I would remove all first letters, keep all numbers and JW

For now I had this

regexp_replace(Business_CONTEXT, '[^0-9]', '')||'JW' RegistrationPoint

But that would include the numbers AFTER 'JW'

Any idea?

Upvotes: 1

Views: 42

Answers (2)

Radagast81
Radagast81

Reputation: 3016

If you really want to extract the longest digit string out of your given strings you can use the following:

  WITH test (Business_CONTEXT) AS 
    (SELECT 'RFID_DP_IDS339020JW3_I9DMsg' from dual union all
     SELECT 'RFID_DP_IDSA72130JW_IDMsg'  from dual union all
     SELECT 'RFID_DP_IDS337310JW1_IDMsg' from dual
    )
  SELECT Business_CONTEXT
       , (SELECT MAX(regexp_substr(Business_CONTEXT, '\d+', 1, LEVEL)) 
                 KEEP (dense_rank last ORDER BY LENGTH(regexp_substr(Business_CONTEXT, '\d+', 1, LEVEL)))
            FROM dual
            CONNECT BY regexp_substr(Business_CONTEXT, '\d+', 1, LEVEL) IS NOT NULL) num
    FROM test

Result:

Business_CONTEXT            | NUM
----------------------------+-----
RFID_DP_IDS339020JW3_I9DMsg | 339020
RFID_DP_IDSA72130JW_IDMsg   |  72130
RFID_DP_IDS337310JW1_IDMsg  | 337310

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143083

How about this?

  • result would return exactly two letters after bunch of digits
  • result2 would return digits + JW

Pick the one you find the most appropriate.

SQL> with test (col) as
  2    (select 'RFID_DP_IDS339020JW3_IDMsg' from dual union all
  3     select 'RFID_DP_IDSA72130JW_IDMsg'  from dual union all
  4     select 'RFID_DP_IDS337310JW1_IDMsg' from dual
  5    )
  6  select col,
  7    regexp_substr(col, '\d+[[:alpha:]]{2}') result,
  8    regexp_substr(col, '\d+JW') result2
  9  from test;

COL                        RESULT                     RESULT2
-------------------------- -------------------------- --------------------------
RFID_DP_IDS339020JW3_IDMsg 339020JW                   339020JW
RFID_DP_IDSA72130JW_IDMsg  72130JW                    72130JW
RFID_DP_IDS337310JW1_IDMsg 337310JW                   337310JW

SQL>

Upvotes: 1

Related Questions