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.
I am using ODI 12c. I have created Variable and i am trying to use this variable value in filter condition to filter the data based on a timestamp. But its not working. Basically what i am trying is from my target table TEST_SDADDR i am trying to get max Timestamp everytime and trying to pass this value in Filter condition so that from source table CSL_STG_DEC_SDADDR, i will always load the latest data into my target table TEST_SDADDR. This is how i have created variable and trying to use Variable in filter: Step1. Step2. Step3. I am getting below error: ODI-1593: The following bind parameters (36:29.0) in the task command are not bound to any value. All the bind parameters should be bound for the command to be successful.
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