Search code examples
sqlregexoracle-sqldeveloper

regual expression for extracting the number

I want to extract amount of memory from table like that.

+---------------+
| product_name  |
+---------------+
| DIMM - 1GB    |
| 8MB Cache /NM |
| RAM - 32 MB   |
| DIMM - 256MB  |
+---------------+

Probably, there is product name can be another number, so I need extract the number near 'GB' or 'MB'. Need to use regexp_replace function. I've got problems with regular expression building.

Solution

  • If you just want to capture the first occurence of consecutive digits in the string, then you can use regexp_substr() as follows:

    regexp_substr(product_name, '\d+')

    You can be more specific with regexp_replace(). The following expression captures sequences of digits followed by MB or GB:

    regexp_replace(product_name, '(^|.*\D)(\d+)\s*[MG]B.*', '\2')

    Demo on DB Fiddle:

    PRODUCT_NAME  | REGEXP_SUBSTR(PRODUCT_NAME,'\D+') | REGEXP_REPLACE(PRODUCT_NAME,'(^|.*\D)(\D+)\S*[MG]B.*','\2')
:------------ | :-------------------------------- | :----------------------------------------------------------
DIMM - 1GB    | 1                                 | 1                                                          
8MB Cache /NM | 8                                 | 8                                                          
RAM - 32 MB   | 32                                | 32