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)
How to replace with null for non alphanumeric characters in Varchar2 field. [message #667633] Tue, 09 January 2018 05:24 Go to next message
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67144
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select translate(col,
                 'A'||translate(col,'#0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','#'),
                 'A')
from tab
/
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 Go to previous messageGo to next message
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 M”R
1 SŽN Y M”R
GLAN Y M”R
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 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
You see how Michel's approach uses a list of allowed characters rather than junk characters?
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 Go to previous messageGo to next message
Bill B
Messages: 1955
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.
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 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Thanks Michel for the script.

Thanks Cookie/Bill also.

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 Go to previous message
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;

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


Current Time: Mon Jun 01 17:04:37 CDT 2020