Return a list of non standard char [message #597952] |
Wed, 09 October 2013 07:28 |
|
jonathanlouisallen
Messages: 4 Registered: October 2013
|
Junior Member |
|
|
I have been tasked with producing a list of all non standard characters that are not one of 0-9, a-z, A-Z from a parts table. The list should capture any non-English characters i.e. áóú etc as well as !"£$%^&* etc. These are a small example of the data.
Part_Description
Probengefäss kompl.
Stützring zu Probenehmer
Zargendichtung m.2 Ventillöcher
Pneumatikdichtung m.2 Ventillöcher
Result from above example
ä.üö
Any help is much appreciated.
|
|
|
|
|
|
|
|
Re: Return a list of non standard char [message #597963 is a reply to message #597961] |
Wed, 09 October 2013 08:00 |
|
Michel Cadot
Messages: 68641 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Like this, for instance:
SQL> with
2 data as (
3 select 'Probengefäss kompl.' val from dual union all
4 select 'Stützring zu Probenehmer' val from dual union all
5 select 'Zargendichtung m.2 Ventillöcher' val from dual union all
6 select 'Pneumatikdichtung m.2 Ventillöcher' val from dual
7 ),
8 vals as (
9 select translate(val,'.abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890 ','.') val
10 from data
11 ),
12 chars as (
13 select distinct substr(val,column_value,1) ch
14 from vals,
15 table(cast(multiset(select level from dual connect by level<=length(val))
16 as sys.odcinumberlist))
17 ),
18 numbered as (
19 select ch, row_number() over (order by ch) rn
20 from chars
21 )
22 select replace(sys_connect_by_path(ch,' '),' ') char_list
23 from numbered
24 where connect_by_isleaf = 1
25 connect by prior rn = rn - 1
26 start with rn = 1
27 /
CHAR_LIST
--------------------------------------------------------------------------------
.äöü
|
|
|
|
Re: Return a list of non standard char [message #597966 is a reply to message #597964] |
Wed, 09 October 2013 08:11 |
|
Michel Cadot
Messages: 68641 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Another similar way:
SQL> with
2 data as (
3 select 'Probengefäss kompl.' val from dual union all
4 select 'Stützring zu Probenehmer' val from dual union all
5 select 'Zargendichtung m.2 Ventillöcher' val from dual union all
6 select 'Pneumatikdichtung m.2 Ventillöcher' val from dual
7 ),
8 all_chars as (
9 select distinct substr(val,column_value,1) ch
10 from data,
11 table(cast(multiset(select level from dual connect by level<=length(val))
12 as sys.odcinumberlist))
13 ),
14 non_standard_chars as (
15 select ch, row_number() over (order by ch) rn
16 from all_chars
17 where instr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890 ',ch) = 0
18 )
19 select replace(sys_connect_by_path(ch,' '),' ') char_list
20 from non_standard_chars
21 where connect_by_isleaf = 1
22 connect by prior rn = rn - 1
23 start with rn = 1
24 /
CHAR_LIST
--------------------------------------------------------------------------------------
.äöü
[Updated on: Wed, 09 October 2013 08:13] Report message to a moderator
|
|
|