2 Replies Latest reply: Aug 26, 2013 4:26 AM by BCV RSS

    SQL for finding chunk or no keyboard values

    879380

      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


          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

            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!