8 Replies Latest reply: Jul 12, 2012 5:22 AM by 576456 RSS

    find Extended ASCII Codes or Character Special on column oracle

    576456
      Hi everyone, i'll want to use query for to find all character special and extended ASCII codes on column

      i have tried this records:

      select * from char_speciale

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

      SURNAME NAME

      SERPI!!     GAB
      SE£     GRA
      ARA%%&     sa
      @dass     dsadsa
      ara€     ssa
      gabriele     dsa

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

      select * from char_speciale
      where regexp_like(surname, '('|| chr(33) || '|'
      || chr(37) || ')')

      ---------------------------------
      SURNAME NAME

      SERPI!!     GAB
      ARA%%&     sa

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

      select * from char_speciale
      where regexp_like(surname,'[[:punct:]]');

      --------------------------------
      SURNAME NAME

      SERPI!!     GAB
      SE£     GRA
      ARA%%&     sa
      @dass     dsadsa
      ara€     ssa

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

      select * from char_speciale WHERE surname != ASCIISTR (surname)

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

      SURNAME NAME

      ara€     ssa
      SE£     GRA


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


      but i don't see just query, because i'll want to find also extended ascii, for example german "o" or french "e", ecc.
      on my table use NLS_CHARACTERSET: AL32UTF8

      i don't know that i have insert just query but i'm not sure.... do have better solution? thank much
        • 1. Re: find Extended ASCII Codes or Character Special on column oracle
          chris227
          Something oldfashioned like this:
          with data as (
          select 'SERPI!! GAB' s from dual union all
          select 'SE£ GRA' from dual union all
          select 'ARA%%& sa' from dual union all
          select '@dass äsadsa' from dual union all
          select 'ara€ ssa' from dual union all
          select 'gabriele dsa' from dual 
          )
          
          select s
          ,  translate(
              lower(s)
              ,'@abcdefghijklmnopqrstuvwxyz1234567890 '
              ,'@'
            ) t
          from data
          where
          length(
            translate(
              lower(s)
              ,'@abcdefghijklmnopqrstuvwxyz1234567890 '
              ,'@'
            )
          )
          < length(s)
          
          S T 
          SERPI!! GAB !! 
          SE£ GRA £ 
          ARA%%& sa %%& 
          @dass äsadsa @ä 
          ara€ ssa € 
          regards
          • 2. Re: find Extended ASCII Codes or Character Special on column oracle
            576456
            i must to write only recordset?
            select 'SERPI!! GAB' s from dual union all
            select 'SE£ GRA' from dual union all
            select 'ARA%%& sa' from dual union all
            select '@dass äsadsa' from dual union all
            select 'ara€ ssa' from dual union all
            select 'gabriele dsa' from dual

            on column have 500.000 recordset.....

            and i have found this:

            select * from char_speciale WHERE surname != ASCIISTR (surname) OR regexp_like(surname,'[[:punct:]]');

            it's work, and how to count on record with character:

            eg: ARA%%& sa
            present 3 char special.

            thanks again

            Edited by: gsflash80 on Jul 11, 2012 5:53 PM
            • 3. Re: find Extended ASCII Codes or Character Special on column oracle
              sb92075
              gsflash80 wrote:
              i must to write only recordset?
              select 'SERPI!! GAB' s from dual union all
              select 'SE£ GRA' from dual union all
              select 'ARA%%& sa' from dual union all
              select '@dass äsadsa' from dual union all
              select 'ara€ ssa' from dual union all
              select 'gabriele dsa' from dual

              on column have 500.000 recordset.....

              and i have found this:

              select * from char_speciale WHERE surname != ASCIISTR (surname) OR regexp_like(surname,'[[:punct:]]');

              it's work, and how to count on record with character:

              eg: ARA%%& sa
              present 3 char special.
              LENGTH(ASCIISTR(SURNAME)-LENGTH(SURNAME)
              • 4. Re: find Extended ASCII Codes or Character Special on column oracle
                576456
                not work.

                ORA-01722: numero non valido
                01722. 00000 - "invalid number"
                *Cause:   
                *Action:


                and i have tried this

                SELECT surname FROM char_speciale WHERE LENGTH ( surname ) < LENGTHB ( surname );

                work.

                but i'll want to:

                SURNAME N_CHAR_SP
                -------------------------------------
                AAAAAA 0 <---------------------------- I DON'T NEED THIS, SHOW ONLY HAVE CHARACTER SPECIAL.
                aAçaaaa 1 <---------------------------- I NEED ONLY THIS
                aAçòòù 4

                thanks again
                • 5. Re: find Extended ASCII Codes or Character Special on column oracle
                  576456
                  have tried this:

                  select cognome, length(cognome)-length(ASCIISTR(cognome)) from char_speciale;

                  COGNOME LENGTH(COGNOME)-LENGTH(ASCIISTR(COGNOME))
                  -------------------- -----------------------------------------
                  SERPI!! 0
                  SE£ -4
                  ARA%%& 0
                  @dass 0
                  ara€ -4
                  gabriele 0
                  ùìòç -16
                  /dsadsa 0
                  $$dsadsa 0
                  marcos 0
                  äreo -4
                  ürore -4
                  • 6. Re: find Extended ASCII Codes or Character Special on column oracle
                    sb92075
                    gsflash80 wrote:
                    have tried this:
                    keep trying.
                    you are getting closer
                    • 7. Re: find Extended ASCII Codes or Character Special on column oracle
                      chris227
                      gsflash80 wrote:
                      select * from char_speciale WHERE surname != ASCIISTR (surname) OR regexp_like(surname,'[[:punct:]]');

                      it's work, and how to count on record with character:
                      Obviously this is not working for all characters you are looking for.
                      Problem is, that you look for a subset of ascii. So you have to define this subset.
                      One way to do this i have shown above.
                      You have just to add all characters allowed after the @. which itself is not allowed.
                      If yoou add the upper letters too you can drop the lower.
                      In my opinion lower and translate are some kind of "low level" operations that should be at least as efficient as regexp-constructs, even for huge amount of data.
                      So what is wrong with
                      with data as (
                      select 'SERPI!! GAB' s from dual union all
                      select 'SE£ GRA' from dual union all
                      select 'ARA%%& sa' from dual union all
                      select '@dass äsadsa' from dual union all
                      select 'ara€ ssa' from dual union all
                      select 'gabriele dsa' from dual 
                      )
                       
                      select
                        s
                      , length(
                         translate(
                          lower(s)
                          ,'@abcdefghijklmnopqrstuvwxyz1234567890 '
                          ,'@'
                         )
                        )
                        count_soec_chars
                      from data
                      where
                      length(
                        translate(
                          lower(s)
                          ,'@abcdefghijklmnopqrstuvwxyz1234567890 '
                          ,'@'
                        )
                      )
                      < length(s)
                      
                      S COUNT_SOEC_CHARS 
                      SERPI!! GAB 2 
                      SE£ GRA 1 
                      ARA%%& sa 3 
                      @dass äsadsa 2 
                      ara€ ssa 1 
                      Of course xou have to replace s with cognome and data with char_speciale in the query.

                      Edited by: chris227 on 12.07.2012 00:47
                      • 8. Re: find Extended ASCII Codes or Character Special on column oracle
                        576456
                        select
                        cognome
                        , length(
                        translate(
                        lower(cognome)
                        ,'@abcdefghijklmnopqrstuvwxyz1234567890 '
                        ,'@'
                        )
                        )
                        conta_char_spec
                        from char_speciale
                        where
                        length(
                        translate(
                        lower(cognome)
                        ,'@abcdefghijklmnopqrstuvwxyz1234567890 '
                        ,'@'
                        )
                        )
                        < length(cognome)

                        thanks much!! It's Work!!! :D :D :D

                        Edited by: gsflash80 on Jul 12, 2012 12:21 PM