CODEWITHSUNDEEP
sqloracle-database
Ali Habib
Ali Habib

Reputation: 17

Extract only the number in reverse till a non-numeric character is reached in a string

I have a scenario to extract vehicle number from plates, to match with a standard database which has consistent numbering format i.e SHJ3/43255

Trying to extract the numbers from the end of string, till a non-numeric char is found

For example:

There are exceptional cases too (where above won't work)

Please help with the regex approach for handling this too.

Upvotes: 0

Views: 119

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

A slight variation, using a single capturing group, and the digits class:

regexp_replace(your_columns, '^.*?([[:digit:]]+)[^[:digit:]]*$', '\1')

db<>fiddle

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269833

This is a little more complicated than I originally thought. You want to match a number before a final character, if the character is there. One method uses regexp_replace():

select col, regexp_replace(col, '^.*([^0-9]|^)([0-9]+)[A-Za-z]?$', '\2')
from (select '27675' as col from dual union all
      select 'SHJ 1/ 5459' from dual union all
      select 'SHJ3.8416' from dual union all
      select '3972W' from dual union all
      select '2SHJ1X' from dual) x

Here is a db<>fiddle.

Upvotes: 2

Related Questions