This discussion is archived
2 Replies Latest reply: Aug 26, 2013 2:26 AM by BCV RSS

SQL for finding chunk or no keyboard values

879380 Newbie
Currently Being Moderated

Hi all ,

 

I have table where users are allowed to enter data from excel sheet but while viewing it in application due to non key board values are like ¶,     ã, £

the validation getting failed and values are not loaded kindly help me in finding those non key board values , as data has been already loaded i need to find the data with non key board values .


i have written the query for one values but i need to find it for all the other values which are not part of key board

select * from note where text like '%'|| chr(1) ||'%';
  • 1. Re: SQL for finding chunk or no keyboard values
    Purvesh K Guru
    Currently Being Moderated


    One way of doing so:

     

    with data as

    (

      select 'some text' col from dual union all

      select 'some text1' col from dual union all

      select 'Invalid Text' || CHR(127) from dual

    )

    select col

      from (

            select col, regexp_substr(col, '.', 1, level) txt

              from data

            connect by col = prior col

                   and level <= length(col)

                   and prior sys_guid() is not null

         )

    where ascii(txt) not between 32 and 126;

     

    COL          

    -------------

    Invalid Text&#127;

  • 2. Re: SQL for finding chunk or no keyboard values
    BCV Journeyer
    Currently Being Moderated

    879380 wrote:

     

    Hi all ,

     

    I have table where users are allowed to enter data from excel sheet but while viewing it in application due to non key board values are like ¶,     ã, £

    the validation getting failed and values are not loaded kindly help me in finding those non key board values , as data has been already loaded i need to find the data with non key board values .

     

    SELECT  REGEXP_REPLACE

              (column_name,

               '[qwertyuiopasdfghjklzxcvbnm QWERTYUIOPASDFGHJKLZXCVBNM ,./?;:''"{}1234567890=!@#$%^&*()_+\|`~-]+'

              ) non_keyborad_symbol_exists

      FROM table_name ;

     

     

    Cheers!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points