Regular Expressions Query

Question

I have to read a string column and load a value in a number column based on the string:

'S12345' - 12345 (Remove 1st alphabet and load the remaining numbers)

'12345' - 12345 (Load the number directly)

'12345T' - NULL (Load NULL if the last character is alphabet)

'1A2B3C45' or 'SQ12345' - NULL (Load NULL if there are more than 1 alphabets in the string)

I am trying to devise a query using CASE and REGEXP_LIKE, with no luck. Can someone please help me with the query for the specific requirement. Thanks in advance..!!

Answer 1

I think you're looking for this:

^[A-Za-z]?(\d+)$

Here is an example with some testdata, you can add your own lines of data to test it.