So we need to find all such records and update the description field with ltrim(rtrim(description)). Īlmost 9000 rows have an data issue - the issue is some users have entered a trailing space using the front end application and now this is causing issue due to some reasons. one particular filed called "Description" which is varchar2(200). I have a table with 48 million rows and database is production database. My question is why "'" character not replaced with ' ' (blank space ) why it removed character ? I am storing all unwanted characters in v9spd900 table in v0010s varchar2(999) fields for ascii range 1 to 255. I am trying to do test removing character from sql by following Do you guess what is the reason ? ( I am running pl/sql code on AIX )ģ. If I am running from SQL to remove/translate character it is getting removed. For other characters pl/sql code working very fine. secondly I am trying translate the characters by pl/sql code as mentioned in this thread but I am not able to remove single quote character from character string. I should do ascii('''') to get an value am I correct ? ( I got ascii value 39 for single quote "'")Ģ. I got an answer what I want to do for question I had asked you in this thread. Table create table t ( str varchar2(30) ) Ģ for x in ( select translate( str, 'a'||demo_pkg.g_bad_chars, 'a' ) str from t )ġ. Package create or replace package body demo_pkg ![]() You'd have to build a string of everything but the ones you wanted to keep in 9i to do this create or replace package demo_pkg If you select thousands of records at a time, you will want to use the view + translate/replace directly for performance. If you select a couple of rows at a time - the PL/SQL function will perform quite adequately. So, now it is like we have a builtin function stripbad that gets rid of our control characters. ![]() PL/SQL procedure successfully select username,ģ translate( username, :undesirable, substr(:undesirable,1,1) ),Ħ where rownum create or replace view bad_charsĪnd now we can select username, replace( translate( username, allbad, onebad ), onebad, '' )ģ where rownum create or replace function stripbad( p_str in varchar2 ) return varchar2ģ undesirable varchar2(25) default 'SAT' ħ translate( p_str, undesirable, substr(undesirable,1,1) ),įunction select username, stripbad( username ) stripped We could then select username, replace( translate( username, 'SAT', 'S' ), 'S', '' )ģ where rownum variable undesirable exec :undesirable := 'SAT' CHR is a function that takes the ASCII code and returns that character - 9 = tab, 13 = CR and so on). Lets say the characters you wanted to remove where 'SAT' (to remove control characters like TABS, CR, LF you would use chr(9) || chr(13) || chr(10). Yes, we can use REPLACE and TRANSLATE to do this.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |