Regular Expressions; return a buried match
in [Oracle]
|
Prev: New dbTrends Software Product for Oracle AWR and STATSPACK Released
Next: How to use REGEXP_SUBSTR to extract owner/obj/part from full obj name ?
From: cate on 3 Feb 2010 17:43 select REGEXP_SUBSTR(encodedField, '^\d{4}(\d\d)') from table; I want the 5th and 6th digit. Can Oracle do this?
From: vsevolod afanassiev on 3 Feb 2010 19:07 To extract 5th and 6th digit: substr(encodedField,5,2)
From: Malcolm Dew-Jones on 3 Feb 2010 18:14 cate (catebekensail(a)yahoo.com) wrote: : select REGEXP_SUBSTR(encodedField, '^\d{4}(\d\d)') from table; : I want the 5th and 6th digit. Can Oracle do this? In 10GR1 not directly in one regexp. In this case substr(result,5,2) would help. In other versions with regexp's I assume the same, but you could always check yourself google: REGEXP_SUBSTR 10GR2 download
From: Maxim Demenko on 4 Feb 2010 02:28 On 03.02.2010 23:43, cate wrote: > select REGEXP_SUBSTR(encodedField, '^\d{4}(\d\d)') from table; > > I want the 5th and 6th digit. Can Oracle do this? Assumed, you always have 6 consequent digits at the beginning, you could do something like this (in pre 10gR2 you have to use [0-9] instead of \d) SQL> with t as ( 2 select '123456def' s from dual union all 3 select '123490def879' from dual 4 ) 5 select s,regexp_replace(s,'^(\d{4})(\d\d).*','\2') r 6 from t; S R -------------------- -------- 123456def 56 123490def879 90 Best regards Maxim
From: Álvaro G. Vicario on 4 Feb 2010 07:26
El 03/02/2010 23:43, cate escribi�/wrote: > select REGEXP_SUBSTR(encodedField, '^\d{4}(\d\d)') from table; > > I want the 5th and 6th digit. Can Oracle do this? I once did similar stuff inside a function. I did something like this: IP1 := TO_NUMBER(REGEXP_SUBSTR(IP_CADENA, '[0-9]+', 1, 1)); IP2 := TO_NUMBER(REGEXP_SUBSTR(IP_CADENA, '[0-9]+', 1, 2)); IP3 := TO_NUMBER(REGEXP_SUBSTR(IP_CADENA, '[0-9]+', 1, 3)); IP4 := TO_NUMBER(REGEXP_SUBSTR(IP_CADENA, '[0-9]+', 1, 4)); This should work on Oracle 10 or greater. -- -- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain -- Mi sitio sobre programaci�n web: http://borrame.com -- Mi web de humor satinado: http://www.demogracia.com -- |