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:
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
i have oracle 9i enterprise release 9.2.0.6.0
ReplyDeletei 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'
try following query:
ReplyDeletewith 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
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
ReplyDeleteto me. Anyhow, I'm definitely glad I found it and I'll be book-marking and checking back frequently!
Wow! In the end I got a blog from where I be capable
ReplyDeleteof really obtain helpful facts concerning my study and knowledge.
Hi, I read your blogs regularly. Your humoristic style is awesome, keep up the good
ReplyDeletework!
This site is my breathing in, rattling superb layout and
ReplyDeletePerfect articles.
I am extremely impressed with your writing talents and also with the
ReplyDeletelayout 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..
Real clean website, appreciate it for this post.
ReplyDeleteSome really interesting points you have written.Assisted me a lot, just what I was looking
ReplyDeletefor :D.
Hey There. I found your weblog using msn. This is a really smartly written article.
ReplyDeleteI will be sure to bookmark it and return to learn extra of your helpful
info. Thank you for the post. I will definitely return.
Perfect piece of work you have done, this internet site
ReplyDeleteis really cool with great info.
As I web-site possessor I believe the content material here is rattling magnificent , appreciate
ReplyDeleteit for your efforts. You should keep it up forever!
Best of luck.
Hey very inteгesting blog!
ReplyDeleteGreat Job Word Files Phone Number and Email Extractor
ReplyDeleteMore valuable post!!! Waiting for the further data regarding this post.
ReplyDeleteContent Writing Course in Chennai
Online Content Writing Course
Thanks for this blog keep sharing your thoughts like this...
ReplyDeleteReact JS Training in Chennai
Reat JS Online Course