sql – Extract a number from a text string

Question:

I would need to know how to extract the value 1, eg. of this text

P93 – HELLO 93 – 1 – ABC

I only need to have that number in a column, the issue is that I have many text cases and it does not return anything.

Examples:

003 – LEVER – 3 – ABC —> From here I need the 3 before – ABC

P93 – HELLO 93 – 1 – ABC —> From here I need the 1 before – ABC

AP3 – ALTO PAKIRMO PATIO COMITUS – DF —> Nothing or Null here

MER – AV. GYVERTADOR 748 – HORLO – 2 – DF —> From here I need the 2 before – DF

Using the following, I got in some cases the value I need, but in many not. For example in P93 – HELLO 93 – 1 – ABC

SELECT 
regexp_substr('TEXTO', '\d+',5)
FROM DUAL

Thanks a lot

Answer:

You could get the expected result using:

  • Creating a regular expression to validate the format and extract the value

     ^.+- (\d+) -.+$

  • REGEXP_LIKE to validate that the string conforms to the format.

  • CASE so that in case the format is not valid, return null

  • REGEXP_REPLACE to extract the value in the string.

Example:

SELECT 
    CASE WHEN REGEXP_LIKE(field, '^.+- (\d+) -.+$') 
         THEN REGEXP_REPLACE(field, '^.+- (\d+) -.+$', '\1') 
         ELSE NULL END AS "value"
FROM table

Demo

Post Views: 0
Scroll to Top