OraFAQ Forum: SQL & PL/SQL » delete non-ASCII characters

Home » SQL & PL/SQL » SQL & PL/SQL » delete non-ASCII characters (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

delete non-ASCII characters [message #595217]

Mon, 09 September 2013 08:06

rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg

Senior Member

In field APPLICATIONNAME are non ASCII characters. Howto delete them?

for example

old value:

René

new value:

Ren

table

CREATE TABLE MIDDLEWARE_CONT
(
  APPLICATIONNAME  VARCHAR2(120 BYTE),
  PRODUCTNAME      VARCHAR2(70 BYTE),
  HOSTNAME         VARCHAR2(60 BYTE),
  VIRTUALHOST      VARCHAR2(60 BYTE),
  HARDWARENAME     VARCHAR2(60 BYTE),
  HWMODEL          VARCHAR2(60 BYTE),
  STANDORT         VARCHAR2(60 BYTE),
  STATUS           VARCHAR2(60 BYTE),
  OS               VARCHAR2(60 BYTE),
  OSVERSION        VARCHAR2(60 BYTE)
)

SELECT count(REPLACE (
          TRANSLATE (SUBSTR (APPLICATIONNAME, 1, 60),
                     CONVERT (SUBSTR (APPLICATIONNAME, 1, 60), 'us7ascii'),
                     'x'),
          'x'))
          non_ascii
  FROM MIDDLEWARE_CONT
 WHERE REPLACE (
          TRANSLATE (SUBSTR (APPLICATIONNAME, 1, 60),
                     CONVERT (SUBSTR (APPLICATIONNAME, 1, 60), 'us7ascii'),
                     'x'),
          'x') IS NOT NULL

result:

Report message to a moderator

Re: delete non-ASCII characters [message #595219 is a reply to message #595217]

Mon, 09 September 2013 08:12

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

You just gave 1 record as an example, are the unwanted characters only appended to the end of the string or they could be anywhere?

Report message to a moderator

Re: delete non-ASCII characters [message #595226 is a reply to message #595217]

Mon, 09 September 2013 08:40

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

update your_table
set your_column = translate(your_column,
                            'A'||translate(your_column, 
                                           chr(0)||'abcdef<... and all the characters you want to keep>',
                                           chr(0)),
                            'A')
/

Regards
Michel

Report message to a moderator

Re: delete non-ASCII characters [message #595231 is a reply to message #595219]

Mon, 09 September 2013 09:12

rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg

Senior Member

Lalit Kumar B wrote on Mon, 09 September 2013 08:12

You just gave 1 record as an example, are the unwanted characters only appended to the end of the string or they could be anywhere?

non ascii char can be every where in text.

old value

Löscher Bet
Summà Ira

new value:

Lscher Bet
Summ Ira

Report message to a moderator

Re: delete non-ASCII characters [message #595232 is a reply to message #595226]

Mon, 09 September 2013 09:14

rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg

Senior Member

Michel Cadot wrote on Mon, 09 September 2013 08:40

update your_table
set your_column = translate(your_column,
                            'A'||translate(your_column, 
                                           chr(0)||'abcdef<... and all the characters you want to keep>',
                                           chr(0)),
                            'A')
/

Regards
Michel

is there a placeholder ('abcdef<... and all the characters you want to keep>') for all ASCII chars? I can't list all the chars allowed.

Report message to a moderator

Re: delete non-ASCII characters [message #595233 is a reply to message #595232]

Mon, 09 September 2013 09:18

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

rc3d wrote on Mon, 09 September 2013 15:14

is there a placeholder ('abcdef<... and all the characters you want to keep>') for all ASCII chars? I can't list all the chars allowed.

Why not?

Report message to a moderator

Re: delete non-ASCII characters [message #595234 is a reply to message #595232]

Mon, 09 September 2013 09:20

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No there is no range syntax with TRANSLATE, you have to list them (there are about 100, not so much).

Regards
Michel

Report message to a moderator

Re: delete non-ASCII characters [message #595235 is a reply to message #595234]

Mon, 09 September 2013 09:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I can't list all the chars allowed.
ASCII values range from 0 to 127; which are not all that many to list.
If you are minorly clever, you could even write code to produce them all for you.

Report message to a moderator

Re: delete non-ASCII characters [message #595236 is a reply to message #595231]

Mon, 09 September 2013 09:46

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

rc3d wrote on Mon, 09 September 2013 19:42

non ascii char can be every where in text.

Ok, so I will edit my query per your requirement. Follow next part.

rc3d wrote on Mon, 09 September 2013 19:44

is there a placeholder ('abcdef<... and all the characters you want to keep>') for all ASCII chars? I can't list all the chars allowed.

Sure, using regular expression a bit of workaround could be avoided -

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE MIDDLEWARE_CONT (  APPLICATIONNAME  VARCHAR2(120 BYTE));

Table created.

SQL>
SQL> insert into middleware_cont values('René');

1 row created.

SQL> insert into middleware_cont values('Löscher Bet');

1 row created.

SQL> insert into middleware_cont values('Summà Ira');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select * from middleware_cont;

APPLICATIONNAME
--------------------------------------------------------------------------------

René
Löscher Bet
Summà Ira

SQL>
SQL> select REGEXP_REPLACE(applicationname,'[^[a-z,A-Z,0-9,chr(0)-chr(127)[:spac
e:]]]*','') my_output from middleware_cont;

MY_OUTPUT
--------------------------------------------------------------------------------

Ren
Lscher Bet
Summ Ira

SQL>

So, since it works, you could update the table -

SQL> update middleware_cont set applicationname=REGEXP_REPLACE(applicationname,'
[^[a-z,A-Z,0-9,chr(0)-chr(127)[:space:]]]*','');

3 rows updated.

SQL> select * from middleware_cont;

APPLICATIONNAME
--------------------------------------------------------------------------------

Ren
Lscher Bet
Summ Ira

SQL>

[Updated on: Mon, 09 September 2013 10:00]

Report message to a moderator

Re: delete non-ASCII characters [message #595240 is a reply to message #595236]

Mon, 09 September 2013 10:30

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Which will be far far slower than mine.

Regards
Michel

Report message to a moderator

Re: delete non-ASCII characters [message #595253 is a reply to message #595240]

Mon, 09 September 2013 12:27

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Completely agree with you Michel. TRANSLATE will definitely be faster than the REGEXP method I demonstrated.

Report message to a moderator

Re: delete non-ASCII characters [message #595306 is a reply to message #595253]

Tue, 10 September 2013 02:07

rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg

Senior Member

Kudos to Lalit Kumar B. Works excellent.

Report message to a moderator

Re: delete non-ASCII characters [message #595308 is a reply to message #595306]

Tue, 10 September 2013 02:15

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Too much lazy developer leads to angry users.
It does NOT work excellent, it just works... for you but NOT for your client just because you are too lazy to type on (almost) all keys of your keyboard. Mad