4 Replies Latest reply: Feb 22, 2012 1:16 PM by user12029647 RSS

    sql: exclude reference to 4 alpha values in sequence

    user12029647
      Hello Guru's
      I am running database version 11.2.0.3.0 attempting to query a database for id's varchar2(8) and I want to exlude any values in the ID field that have sucessive alpha values in a row (if a letter exists from position 1 through 4 on the id field). For example I would want to exclude a value like SUNS from my result set, I hope you get the idea.
      Basically if the first 4 positions are all letters I do not want to see them in the result set.
      Is there a fast way to do this?
      select * from test where substr(id) ????

      Thank you in advance. :-)
        • 1. Re: sql: exclude reference to 4 alpha values in sequence
          sb92075
          user12029647 wrote:
          Hello Guru's
          I am running database version 11.2.0.3.0 attempting to query a database for id's varchar2(8) and I want to exlude any values in the ID field that have sucessive alpha values in a row (if a letter exists from position 1 through 4 on the id field). For example I would want to exclude a value like SUNS from my result set, I hope you get the idea.
          Basically if the first 4 positions are all letters I do not want to see them in the result set.
          Is there a fast way to do this?
          select * from test where substr(id) ????

          Thank you in advance. :-)
          use MINUS
          • 2. Re: sql: exclude reference to 4 alpha values in sequence
            Solomon Yakobson
            select * from test where NOT REGEXP_LIKE(id,'^[[:alpha:]]{4}');

            For example:
            with test as (
                          select 'SUNS123' id from dual union all
                          select 'SUN12345' from dual union all
                          select '#SUNS99' from dual
                         )
            select * from test where NOT REGEXP_LIKE(id,'^[[:alpha:]]{4}')
            /
            
            ID
            --------
            SUN12345
            #SUNS99
            
            SQL> 
            SY.

            Edited by: Solomon Yakobson on Feb 22, 2012 2:07 PM
            • 3. Re: sql: exclude reference to 4 alpha values in sequence
              Frank Kulash
              Hi,

              Here's one way:
              SELECT     *
              FROM     table_x
              WHERE     LTRIM ( UPPER (SUBSTR (id || '0', 1, 4))
                         , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
                         ) IS NOT NULL
              Depending on what you know about id (for example, it's never NULL, or it never contains lower-case letters) you might be able to simplify this a little.

              You could also use regular expressions, but regular expressions will probably be slower.
              • 4. Re: sql: exclude reference to 4 alpha values in sequence
                user12029647
                Thanks to all...Solomon, you saved me hours of study time..I am looking up what else I can do with reg_exp.
                Cheers!!!!!
                Thanks a billion.