Hey, hoping to get a bit of help with regular expressions, my regexp-fu is weak
So I have this query: (11.2.0.2.0)
with xx as (
select 'the' x from dual union all
select 'quick é' from dual union all
select 'brown ç' from dual union all
select '123 SOMEWHERE CT' from dual union all
select 'fox Ç' from dual union all
select 'jumped 3' from dual union all
select 'over' from dual )
select x,
regexp_instr ( x, '[^[:alpha:]]' ),
regexp_instr ( x, '[^[:alnum:]]' )
from xx
which returns:
X REGEXP_INSTR(X,'[^[:ALPHA:]REGEXP_INSTR(X,'[^[:ALNUM:]]')
---------------- ------------------------------ ------------------------------
the 0 0
quick é 6 6
brown ç 6 6
123 SOMEWHERE CT 1 4
fox Ç 4 4
jumped 3 7 7
over 0 0
7 rows selected.
I understand this so far, and it looks good.
Now, the purpose to this, is to try to id those pesky "non-english" characters (I'm actually trying to search for the french characters for some testing )
However, as it stand, using just [^[:ALNUM:]] picks out the spaces as well .. how can I ignore spaces from this as well?
I tried:
regexp_instr ( x, '[^[:alnum:][:space:]]' )
regexp_instr ( x, '[^[:alnum:]^[:space:]]' )
and
regexp_instr ( x, '[^[:alnum:]][^[:space:]]' )
all without success. I'm sure I'm just missing something simple. I tried doing some searches around, but couldn't find anything that helped.
Hoping somebody can help me out?
Thanks!
I'd like to get to here:
X REGEXP_INSTR(???)
---------------- ------------------------------
the 0
quick é 6
brown ç 6
123 SOMEWHERE CT 0
fox Ç 4
jumped 3 0
over 0
7 rows selected.
Since from there, I can move that into the WHERE clause and just pick out the > 0 items.
And ultimately, I'm looking for this final result:
X
----------------
quick é
brown ç
fox Ç
3 rows selected.
Message was edited by: Greg.Spall
Hmm, seems it only seeing the spaces - it's not seeing the french characters - so I'm worse off than I thought :P *sigh*.