Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2008
    Posts
    5

    Unanswered: Removing invalid characters from database

    Hi all,

    how do i remove the invalid charecters from the database? They should be replaced with null. The invalid utf-8 charecters. My problem is that while sending the data to the clients, the data is showing error in some places while opening that in internet explorer. The parsing of data should be done, how can i do it? Can you please help me out in this??

    Preethi

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Did you consider use of the REPLACE function?

    For example, if you'd like to replace all 'x' with a null, you'd
    Code:
    SELECT REPLACE(column_name, 'x', '') FROM some_table
    (of course, change would require UPDATE, not SELECT)

  3. #3
    Join Date
    Feb 2008
    Posts
    5

    reply

    what i want is that there should be a mechanism where in the entire databse has to be searched automatically searched and should be replaced with null.., thati s the parsing of data should take place.....

    i tried with csscan but it would not replace the invalid characters with null. the database should follow utf-8 standards

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    How about if you just change the database character set ?

  5. #5
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Try using something like this:

    PHP Code:
    SET PAGESIZE 0;
    SPOOL some_file.sql

    SELECT 
    'UPDATE TABLE ' || table_name || ' SET ' || column_name || 
    '= REGEXP_REPLACE( ' || column_name || ', ''[:cntrl:]'');'
    FROM user_tab_cols;

    SPOOL OFF
    This will create your file "some_file.sql" with a series of UPDATE statements like this:

    PHP Code:
    UPDATE TABLE your_table SET column1=REGEXP_REPLACE(column1'[:cntrl:]');
    UPDATE TABLE your_table SET column2=REGEXP_REPLACE(column2'[:cntrl:]'); 
    NOTE: in the first SELECT statement... ''[:cntrl:]'' is using two (2) single quotes, not a double-quote.

    Check the .SQL file created and test a couple of statements before running the entire file as a script...
    JoeB
    save disk space, use smaller fonts

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •