OraFAQ Forum: SQL & PL/SQL » How to replace with null for non alphanumeric characters in Varchar2 field.

Home » SQL & PL/SQL » SQL & PL/SQL » How to replace with null for non alphanumeric characters in Varchar2 field. (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)

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

How to replace with null for non alphanumeric characters in Varchar2 field. [message #667633]

Tue, 09 January 2018 05:24

msol25
Messages: 396
Registered: June 2011

Senior Member

Hi,

I have to replace which is not alphanumeric character in description field.Actually in my case some non-english character is coming.I want to remove all those special characters.Please suggest.

Report message to a moderator

Re: How to replace with null for non alphanumeric characters in Varchar2 field. [message #667636 is a reply to message #667633]

Tue, 09 January 2018 05:59

Michel Cadot
Messages: 68510
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

select translate(col,
                 'A'||translate(col,'#0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','#'),
                 'A')
from tab
/

Report message to a moderator

Re: How to replace with null for non alphanumeric characters in Varchar2 field. [message #667642 is a reply to message #667636]

Tue, 09 January 2018 06:46

msol25
Messages: 396
Registered: June 2011

Senior Member

Thanks Michel,

I should have the list of special characters in advance which is coming in the field. But, I have millions of records and difficult to find out the non english characters in each field.I find out few characters in below query and used to check the incoming data in field:


select *
from   test_tab 
where   regexp_like(field_name,'(Å|Ã|¶|Ž|¿|\+||||Â|M)');


Output:

Filed_name
----------

1 SŽN Y GWYNT
2 CWM DŽR COTTAGE
3 SŽN Y GWYNT
1 SŽN YR AFON
2 HAUL A MR
1 SŽN Y MR
GLAN Y MR
8 SŽN Y WYLAN

But, Still scared if some other characters will also be coming then we have to execute the script in production once again.So, Just want to know is there possibility to remove non-alphanumeric characters without passing list of identified junk characters for replacement?.

[Updated on: Tue, 09 January 2018 06:48]

Report message to a moderator

Re: How to replace with null for non alphanumeric characters in Varchar2 field. [message #667643 is a reply to message #667642]

Tue, 09 January 2018 06:49

cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester

Senior Member

You see how Michel's approach uses a list of allowed characters rather than junk characters?

Report message to a moderator

Re: How to replace with null for non alphanumeric characters in Varchar2 field. [message #667644 is a reply to message #667643]

Tue, 09 January 2018 07:00

Bill B
Messages: 1971
Registered: December 2004

Senior Member

if you don't want to alter your application to not allow any USASCII characters then make an on insert, update trigger on the table and use Michel's code as a base to automatically correct the data as it is entered or updated in the table.

Report message to a moderator

Re: How to replace with null for non alphanumeric characters in Varchar2 field. [message #667646 is a reply to message #667644]

Tue, 09 January 2018 07:03

msol25
Messages: 396
Registered: June 2011

Senior Member

Thanks Michel for the script.

Thanks Cookie/Bill also.

Report message to a moderator

Re: How to replace with null for non alphanumeric characters in Varchar2 field. [message #667790 is a reply to message #667646]

Tue, 16 January 2018 02:57

msol25
Messages: 396
Registered: June 2011

Senior Member

Thanks Michel,

I can use translate function for getting junk characters and replace those characters.I created below query for finding any other special incoming characters in the table column.If we find any character is valid then we can pass that character into our lookup list.If not, then we can find the list of junk characters for a specific field in the table.I hope, below query can also helpful for my other colleagues to find junk characters in a specific table column.


select  col_junk
       ,count(1)
from
(
   select  tst.col_val
          ,translate(col_val,'#0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz()[]-.\, ','#') as col_junk
  from   test_table tst
)
group by col_junk;

Report message to a moderator

Previous Topic:	Problem with Data display
Next Topic:	Material view auto refresh DBMS_SCHEDULER

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Oct 01 10:24:02 CDT 2023