Oracle: Extract Numbers from String (Ex: Pin from Address)

SELECT REGEXP_SUBSTR(REPLACE(ADDRESS,' '),'[[:digit:]]{6}') FROM MYTABLE
WHERE REGEXP_SUBSTR(REPLACE(ADDRESS,' '),'[[:digit:]]{6}') IS NOT NULL;

Explanation:
It will extract the number (6 adjcent digits) from the address field


If you want to extact all numbers from string use any of below:
SELECT regexp_replace('12ZXC3ASD456FGH8TED63','\D') FROM dual;
SELECT regexp_replace('12ZXC3ASD456FGH8TED63','[^0-9]') FROM dual; 
SELECT regexp_replace('12ZXC3ASD456FGH8TED63','[^[:digit:]]') FROM dual;


Related Links:
- Oracle: Function ISNUMERIC Check Value is Number or Not
- Extracting text between html tags (removing html tags)
- Oracle: Extract Initials of Names






16 comments:

  1. i have oracle 9i enterprise release 9.2.0.6.0

    i have remarks field where BMI details are written along with other important information. I want to extract the BMI detail and do some comparision ... like how many less than 30 .. how many greater than 30 but less than 45 and how many above 45.

    my required data is written anywhere in the string, after word BMI (some times 'BMI 20' and space and then number, and some time 'BMI:20' and sometimes 'BMI: 20')


    ==Sample data==
    'patient improving weight 70 BMI 24 healthy B+'
    'akdhj asdkh BMI24 asdflkh asdflkh'
    'asdhkl asdkh BP_D 30 dlajkd BMI:24 aslkh a34'
    'BMI: 24 aldkj asldkj 23 asdfkl334 345 34534'

    ReplyDelete
  2. try following query:

    with t as
    (
    select 'patient improving weight 70 BMI 24 healthy B+' x from dual union
    select 'akdhj asdkh BMI24 asdflkh asdflkh' x from dual union
    select 'asdhkl asdkh BP_D 30 dlajkd BMI:24 aslkh a34' x from dual union
    select 'BMI: 24 aldkj asldkj 23 asdfkl334 345 34534' x from dual
    ) SELECT
    trim(translate(SUBSTR(X,INSTR(X,'BMI')+3,5),translate(SUBSTR(X,INSTR(X,'BMI')+3,5),'1234567890',' '),' '))
    from t

    ReplyDelete
  3. Hi! I could have sworn I've been to this blog before but after checking through some of the post I realized it's new
    to me. Anyhow, I'm definitely glad I found it and I'll be book-marking and checking back frequently!

    ReplyDelete
  4. Wow! In the end I got a blog from where I be capable
    of really obtain helpful facts concerning my study and knowledge.

    ReplyDelete
  5. Hi, I read your blogs regularly. Your humoristic style is awesome, keep up the good
    work!

    ReplyDelete
  6. This site is my breathing in, rattling superb layout and
    Perfect articles.

    ReplyDelete
  7. I am extremely impressed with your writing talents and also with the
    layout for your blog. Is that this a paid subject or did you
    modify it your self? Either way stay up the excellent high quality writing, it's uncommon to
    look a great blog like this one these days..

    ReplyDelete
  8. Real clean website, appreciate it for this post.

    ReplyDelete
  9. Some really interesting points you have written.Assisted me a lot, just what I was looking
    for :D.

    ReplyDelete
  10. Hey There. I found your weblog using msn. This is a really smartly written article.
    I will be sure to bookmark it and return to learn extra of your helpful
    info. Thank you for the post. I will definitely return.

    ReplyDelete
  11. Perfect piece of work you have done, this internet site
    is really cool with great info.

    ReplyDelete
  12. As I web-site possessor I believe the content material here is rattling magnificent , appreciate
    it for your efforts. You should keep it up forever!
    Best of luck.

    ReplyDelete
  13. Hey very inteгesting blog!

    ReplyDelete