Extract number from string with Oracle function

Question

I need to create an Oracle DB function that takes a string as parameter  The string contains letters and numbers  I need to extract all the numbers from this string  For example  if I have a string like RO1234  I need to be able to use a function  say extract number  RO1234    and the result would be 1234    To be even more precise  this is the kind of SQL query which this function would be used in   SELECT DISTINCT column name  extract number column name   FROM table name WHERE extract number column name    1234    QUESTION  How do I add a function like that to my Oracle database  in order to be able to use it like in the example above  using any of Oracle SQL Developer or SQLTools client applications

User · Answer

You can use regular expressions for extracting the number from string  Lets check it  Suppose this is the string mixing text and numbers  stack12345overflow569   This one should work   select regexp replace  stack12345overflow569       alpha        as numbers from dual    which will return  12345569    also you can use this one   select regexp replace  stack12345overflow569      0-9        as numbers         regexp replace  Stack12345OverFlow569      a-z and  A-Z        as characters from dual   which will return  12345569  for numbers and  StackOverFlow  for characters

User · Answer

This works for me  I only need first numbers in string  TO NUMBER regexp substr h HIST OBSE         digit          digit         the field had the following string   quot  43 Paginas  REGLAS DE PARTICIPACION quot   result field  43

User · Answer

If you are looking for 1st Number with decimal as string has correct decimal places  you may try regexp substr function like this   regexp substr  stack12 345overflow          digit          digit

User · Answer

You d use REGEXP REPLACE in order to remove all non-digit characters from a string   select regexp replace column name     0-9        from mytable    or  select regexp replace column name       digit          from mytable    Of course you can write a function extract number  It seems a bit like overkill though  to write a funtion that consists of only one function call itself   create function extract number in number varchar2  return varchar2 is begin   return regexp replace in number       digit           end

[sql] Extract number from string with Oracle function

Examples related to sql

Examples related to oracle

Examples related to oracle11g

Examples related to oracle-sqldeveloper

Examples related to sqltools