For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
Where can I find the following software:
- Oracle 12c (12.1.0.1.0) client 32-bit
- ODAC 12.1.0.1.0 32-bit
Thanks.
REGEXP_INSTR(<col_name>, '[[:alpha:] -]')
SELECT * FROM <TABLE> WHERE REGEXP_LIKE(<COLUMN>, '[a-z -][A-Z -]');
[a-zA-Z -]
[[:alpha:] -]
SQL> select level-1 as asc_code, decode(chr(level-1), regexp_substr(chr(level-1), '[[:print:]]'), CHR(level-1)) as chr, 2 decode(chr(level-1), regexp_substr(chr(level-1), '[[:graph:]]'), 1) is_graph, 3 decode(chr(level-1), regexp_substr(chr(level-1), '[[:blank:]]'), 1) is_blank, 4 decode(chr(level-1), regexp_substr(chr(level-1), '[[:alnum:]]'), 1) is_alnum, 5 decode(chr(level-1), regexp_substr(chr(level-1), '[[:alpha:]]'), 1) is_alpha, 6 decode(chr(level-1), regexp_substr(chr(level-1), '[[:digit:]]'), 1) is_digit, 7 decode(chr(level-1), regexp_substr(chr(level-1), '[[:cntrl:]]'), 1) is_cntrl, 8 decode(chr(level-1), regexp_substr(chr(level-1), '[[:lower:]]'), 1) is_lower, 9 decode(chr(level-1), regexp_substr(chr(level-1), '[[:upper:]]'), 1) is_upper, 10 decode(chr(level-1), regexp_substr(chr(level-1), '[[:print:]]'), 1) is_print, 11 decode(chr(level-1), regexp_substr(chr(level-1), '[[:punct:]]'), 1) is_punct, 12 decode(chr(level-1), regexp_substr(chr(level-1), '[[:space:]]'), 1) is_space, 13 decode(chr(level-1), regexp_substr(chr(level-1), '[[:xdigit:]]'), 1) is_xdigit 14 from dual 15 connect by level <= 256 16 / ASC_CODE C IS_GRAPH IS_BLANK IS_ALNUM IS_ALPHA IS_DIGIT IS_CNTRL IS_LOWER IS_UPPER IS_PRINT IS_PUNCT IS_SPACE IS_XDIGIT ---------- - ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 0 1 1 1 2 1 3 1 4 1 5 1 6 1 7 1 8 1 9 1 1 10 1 1 11 1 1 12 1 1 13 1 1 14 1 15 1 16 1 17 1 18 1 19 1 20 1 21 1 22 1 23 1 24 1 25 1 26 1 27 1 28 1 29 1 30 1 31 1 32 1 1 1 33 ! 1 1 1 34 " 1 1 1 35 # 1 1 1 36 $ 1 1 1 37 % 1 1 1 38 & 1 1 1 39 ' 1 1 1 40 ( 1 1 1 41 ) 1 1 1 42 * 1 1 1 43 + 1 1 1 44 , 1 1 1 45 - 1 1 1 46 . 1 1 1 47 / 1 1 1 48 0 1 1 1 1 1 49 1 1 1 1 1 1 50 2 1 1 1 1 1 51 3 1 1 1 1 1 52 4 1 1 1 1 1 53 5 1 1 1 1 1 54 6 1 1 1 1 1 55 7 1 1 1 1 1 56 8 1 1 1 1 1 57 9 1 1 1 1 1 58 : 1 1 1 59 ; 1 1 1 60 < 1 1 1 61 = 1 1 1 62 > 1 1 1 63 ? 1 1 1 64 @ 1 1 1 65 A 1 1 1 1 1 1 66 B 1 1 1 1 1 1 67 C 1 1 1 1 1 1 68 D 1 1 1 1 1 1 69 E 1 1 1 1 1 1 70 F 1 1 1 1 1 1 71 G 1 1 1 1 1 72 H 1 1 1 1 1 73 I 1 1 1 1 1 74 J 1 1 1 1 1 75 K 1 1 1 1 1 76 L 1 1 1 1 1 77 M 1 1 1 1 1 78 N 1 1 1 1 1 79 O 1 1 1 1 1 80 P 1 1 1 1 1 81 Q 1 1 1 1 1 82 R 1 1 1 1 1 83 S 1 1 1 1 1 84 T 1 1 1 1 1 85 U 1 1 1 1 1 86 V 1 1 1 1 1 87 W 1 1 1 1 1 88 X 1 1 1 1 1 89 Y 1 1 1 1 1 90 Z 1 1 1 1 1 91 [ 1 1 1 92 \ 1 1 1 93 ] 1 1 1 94 ^ 1 1 1 95 _ 1 1 1 96 ` 1 1 1 97 a 1 1 1 1 1 1 98 b 1 1 1 1 1 1 99 c 1 1 1 1 1 1 100 d 1 1 1 1 1 1 101 e 1 1 1 1 1 1 102 f 1 1 1 1 1 1 103 g 1 1 1 1 1 104 h 1 1 1 1 1 105 i 1 1 1 1 1 106 j 1 1 1 1 1 107 k 1 1 1 1 1 108 l 1 1 1 1 1 109 m 1 1 1 1 1 110 n 1 1 1 1 1 111 o 1 1 1 1 1 112 p 1 1 1 1 1 113 q 1 1 1 1 1 114 r 1 1 1 1 1 115 s 1 1 1 1 1 116 t 1 1 1 1 1 117 u 1 1 1 1 1 118 v 1 1 1 1 1 119 w 1 1 1 1 1 120 x 1 1 1 1 1 121 y 1 1 1 1 1 122 z 1 1 1 1 1 123 { 1 1 1 124 | 1 1 1 125 } 1 1 1 126 ~ 1 1 1 127 1 128 Ç 1 1 1 etc. {code}
Aketi Jyuuzou Oct 27 2010 — edited on Oct 27 2010 I like this Regex book ;-) http://oreilly.com/catalog/9780596528126/ with t(Val) as( select 'abc' from dual union all select 'ABC' from dual union all select '-' from dual) select Val, case when RegExp_Like(Val,'[- a-zA-Z]') then 1 else 0 end as IsMatch from t; VAL IsMatch --- ------- abc 1 ABC 1 - 1 MichaelS Oct 27 2010 If your language settings support it you might use the somewhat shorter regexp_like (val, '[- a-Z]') instead of regexp_like (val, '[- a-zA-Z]') BluShadow Oct 27 2010 MichaelS wrote: If your language settings support it you might use the somewhat shorter regexp_like (val, '[- a-Z]') Yeah, but most people will have standard ASCII chr sets so "a-Z" will give them some extra characters they weren't expecting. MichaelS Oct 27 2010 so "a-Z" will give them some extra characters they weren't expecting.Don't think there'll be any different characters between [a-zA-Z] and [a-Z]: I think it is more just a matter of how the character class was implemented. It just seems it is not supoorted for every language: SQL> alter session set nls_language=german / Session altered. SQL> with t (val) as ( select 'abc' from dual union all select 'ABC' from dual union all select 'Ab-C' from dual union all select '-' from dual ) -- -- select val, case when regexp_like (val, '[- [a-Zthen 1 else 0 end as ismatch from t / VAL ISMATCH ---- ---------- abc 1 ABC 1 Ab-C 1 - 1 4 rows selected. SQL> alter session set nls_language=english / Session altered. SQL> with t (val) as ( select 'abc' from dual union all select 'ABC' from dual union all select 'Ab-C' from dual union all select '-' from dual ) -- -- select val, case when regexp_like (val, '[- [a-Zthen 1 else 0 end as ismatch from t * Error at line 9 ORA-12728: invalid range in regular expression BluShadow Oct 27 2010 — edited on Oct 27 2010 MichaelS wrote: so "a-Z" will give them some extra characters they weren't expecting.Don't think there'll be any different characters between [a-zA-Z] and [a-Z]Ooo, but there is (see the list of ASCII character in my post above)... SQL> ed Wrote file afiedt.buf 1 with t as (select 'HeresMyAlphaStringwith]anothercharacter' as txt from dual) 2 -- 3 select case when regexp_like(txt,'^[A-z]+$') then 'like A-z' else null end as "Atoz" 4 ,case when regexp_like(txt,'^[a-zA-Z]+$') then 'like a-zA-Z' else null end as "atozAtoZ" 5* from t SQL> / Atoz atozAtoZ -------- ----------- like A-z SQL>"A-z" (rather than "a-Z" because ASCII "A" is before ASCII "z" in English characterset) contains other characters like "]" etc. Specifying the two sets "a-z" and "A-Z" is more specific and limiting which gives what is expected. John Spencer Oct 27 2010 Michael: It is not that it is unimplemented, its just that you asked it to do something unreasonable. My nls_language is english SQL> with t as ( 2 select 'a' val from dual union all 3 select 'Z' from dual union all 4 select 'A' from dual union all 5 select 'z' from dual) 6 SELECT val FROM t 7 ORDER BY val; V - A Z a z SQL> with t as ( 2 select 'a' val from dual union all 3 select 'Z' from dual union all 4 select 'A' from dual union all 5 select 'z' from dual 6 SELECT val FROM t 7 ORDER BY NLSSORT(val, 'NLS_SORT = German'); V - a A z ZJohn MichaelS Oct 27 2010 Thanks John, Blu - all makes sense now ;)
with t(Val) as( select 'abc' from dual union all select 'ABC' from dual union all select '-' from dual) select Val, case when RegExp_Like(Val,'[- a-zA-Z]') then 1 else 0 end as IsMatch from t; VAL IsMatch --- ------- abc 1 ABC 1 - 1
regexp_like (val, '[- a-Z]')
regexp_like (val, '[- a-zA-Z]')
SQL> alter session set nls_language=german / Session altered. SQL> with t (val) as ( select 'abc' from dual union all select 'ABC' from dual union all select 'Ab-C' from dual union all select '-' from dual ) -- -- select val, case when regexp_like (val, '[- [a-Zthen 1 else 0 end as ismatch from t / VAL ISMATCH ---- ---------- abc 1 ABC 1 Ab-C 1 - 1 4 rows selected. SQL> alter session set nls_language=english / Session altered. SQL> with t (val) as ( select 'abc' from dual union all select 'ABC' from dual union all select 'Ab-C' from dual union all select '-' from dual ) -- -- select val, case when regexp_like (val, '[- [a-Zthen 1 else 0 end as ismatch from t * Error at line 9 ORA-12728: invalid range in regular expression
SQL> ed Wrote file afiedt.buf 1 with t as (select 'HeresMyAlphaStringwith]anothercharacter' as txt from dual) 2 -- 3 select case when regexp_like(txt,'^[A-z]+$') then 'like A-z' else null end as "Atoz" 4 ,case when regexp_like(txt,'^[a-zA-Z]+$') then 'like a-zA-Z' else null end as "atozAtoZ" 5* from t SQL> / Atoz atozAtoZ -------- ----------- like A-z SQL>
SQL> with t as ( 2 select 'a' val from dual union all 3 select 'Z' from dual union all 4 select 'A' from dual union all 5 select 'z' from dual) 6 SELECT val FROM t 7 ORDER BY val; V - A Z a z SQL> with t as ( 2 select 'a' val from dual union all 3 select 'Z' from dual union all 4 select 'A' from dual union all 5 select 'z' from dual 6 SELECT val FROM t 7 ORDER BY NLSSORT(val, 'NLS_SORT = German'); V - a A z Z
1 - 10