9 Replies Latest reply: Jan 12, 2013 4:58 AM by Etbin RSS

    reg expr

    Rahul_India
      meaning of this query
      SELECT t.col1
      FROM t
      WHERE NOT REGEXP_LIKE(t.col1, '^[0-9]+$')
      ;

      how is ^ and $ working?
        • 1. Re: reg expr
          Frank Kulash
          Hi,
          Rahul India wrote:
          meaning of this query
          SELECT t.col1
          FROM t
          WHERE NOT REGEXP_LIKE(t.col1, '^[0-9]+$')
          ;

          how is ^ and $ working?
          ^ means the beginning of the string.
          $ means the end of the string.

          The pattern matches a string of 1 or more digits (and nothing except digits).
          Since it's NOT REGEXP_LIKE, that means the query is finding rows where col1 does not consist entirely of digits, 0 through 9.
          • 2. Re: reg expr
            Rahul_India
            so frank ^ and $ will always exist together
            • 3. Re: reg expr
              Frank Kulash
              Hi,
              Rahul India wrote:
              so frank ^ and $ will always exist together
              No.
              If you want to find rows where col1 starts with a number, regardless of what (if anything) comes after it, then you would use only ^
              WHERE   REGEXP_LIKE (col1, '^[0-9]')
              If you want to find rows where col1 ends with a number, regardless of what (if anything) comes before, then you would use only $
              WHERE   REGEXP_LIKE (col1, '[0-9]$')
              If you want to find rows where col1 contains a number, regardless of what (if anything) comes before or after it, then you would use neither ^ nor $.
              WHERE   REGEXP_LIKE (col1, '[0-9]')
              • 4. Re: reg expr
                Rahul_India
                CREATE TABLE test (
                testcol VARCHAR2(50));
                
                INSERT INTO test VALUES ('abcde');
                INSERT INTO test VALUES ('12345');
                INSERT INTO test VALUES ('1a4A5');
                INSERT INTO test VALUES ('12a45');
                INSERT INTO test VALUES ('12aBC');
                INSERT INTO test VALUES ('12abc');
                INSERT INTO test VALUES ('12ab5');
                INSERT INTO test VALUES ('12aa5');
                INSERT INTO test VALUES ('12AB5');
                INSERT INTO test VALUES ('ABCDE');
                INSERT INTO test VALUES ('123-5');
                INSERT INTO test VALUES ('12.45');
                INSERT INTO test VALUES ('1a4b5');
                INSERT INTO test VALUES ('1 3 5');
                INSERT INTO test VALUES ('1  45');
                INSERT INTO test VALUES ('1   5');
                INSERT INTO test VALUES ('a  b  c  d');
                INSERT INTO test VALUES ('a b  c   d    e');
                INSERT INTO test VALUES ('a              e');
                INSERT INTO test VALUES ('Steven');
                INSERT INTO test VALUES ('Stephen');
                INSERT INTO test VALUES ('111.222.3333');
                INSERT INTO test VALUES ('222.333.4444');
                INSERT INTO test VALUES ('333.444.5555');
                INSERT INTO test VALUES ('abcdefabcdefabcxyz');
                COMMIT;
                how to get just the rows with numbers
                • 5. Re: reg expr
                  Frank Kulash
                  Hi,
                  Rahul India wrote:
                  CREATE TABLE test (
                  testcol VARCHAR2(50));
                  
                  INSERT INTO test VALUES ('abcde');
                  ...
                  Thanks for posting the CREATE TABLE and INSERT statements.
                  Don't forget to post the results you want from that data.
                  how to get just the rows with numbers
                  That depends on what you mean.
                  Do you mean the rows where testcol contains a number, regardless of what (if anything) comes before or after it?
                  That is, are the the results you want?
                  TESTCOL
                  --------------------------------------------------
                  1   5
                  1  45
                  1 3 5
                  111.222.3333
                  12.45
                  123-5
                  12345
                  12AB5
                  12a45
                  12aBC
                  12aa5
                  12ab5
                  12abc
                  1a4A5
                  1a4b5
                  222.333.4444
                  333.444.5555
                  These are the rows with numbers. As you can see, some of them have other text besides numbers, too, but the have numbers.

                  If that's what you want, here's one way to get it:
                  SELECT    testcol
                  FROM        test
                  WHERE        REGEXP_LIKE ( testcol
                                  , '[0-9]'
                                  )
                  ORDER BY  testcol
                  ;
                  Remember that regular expressions are a lot less efficient that less powerful functions. A faster way to get the same results is:
                  SELECT       testcol
                  FROM       test
                  WHERE       NVL ( TRANSLATE ( testcol
                                         , 'A0123456789'
                                       , 'A'
                                       )
                             , '?'
                             )     != testcol
                  ORDER BY  testcol
                  ;
                  • 6. Re: reg expr
                    Rahul_India
                    Frank i want those rows which do not contain alphabets.

                    so we should avoid using regular expression if it less efficient?
                    SELECT    testcol
                    FROM        test
                    WHERE      not  REGEXP_LIKE ( testcol
                                    , '[a-z,A-Z]'
                                    )
                    ORDER BY  testcol
                    ;
                    This is the one way to get number.But i want the other way

                    Edited by: Rahul India on Jan 12, 2013 4:34 AM

                    Edited by: Rahul India on Jan 12, 2013 4:37 AM
                    • 7. Re: reg expr
                      Frank Kulash
                      Hi,
                      Rahul India wrote:
                      Frank i want those rows which do not contain alphabets.
                      Once again, post the results you want.

                      Is '.' an "alphabet"? How about ' ' (space)?
                      so we should avoid using regular expression if it less efficient?
                      You should realize that regular expressions are often less efficient than other ways.
                      Sometimes I use regular expressions when the speed is acceptable, even though there might be faster ways. Speed is just one consideration. Another important one is how easy the code is to debug and maintain.
                      SELECT    testcol
                      FROM        test
                      WHERE      not  REGEXP_LIKE ( testcol
                                      , '[a-z,A-Z]'
                                      )
                      ORDER BY  testcol
                      ;
                      This is the one way to get number.
                      That won't necessary get numbers; that will just look for rows where testcol does not contain any letters or commas. It will accept strings that contain punctuation and whitespace, such as ' ~!@#$ &*()= .' It doesn't say anything at all about numbers (though, in the set of sample data you posted, it may happen to get the results you want.)
                      But i want the other way
                      Do you mean an other way?
                      This is equivalent to what you posted:
                      SELECT       testcol
                      FROM       test
                      WHERE       UPPER (testcol)     = LOWER (testcol)
                      AND       testcol          nOT LIKE '%,%'
                      ORDER BY  testcol
                      ;
                      but I don't believe it's what you want.
                      • 8. reg expr
                        861115
                        21, 3rd Cross, Besthara Block, Vidyaranya Puram
                        316, Harishchandra Ghat Road, K N Pura
                        314, Harishchandra Ghat Road, K N Pura
                        93, S P Road, Vidyaranya Puram


                        I have some data in this format in a column .

                        I want to make a column in select query where only cross will be comeing where cross is available..

                        like

                        CROSS
                        3rd cross (those who donot have cross null will be comeing)...



                        cross may be at any position not in 2nd part for all the columns..
                        ...
                        • 9. Re: reg expr
                          Etbin
                          Maybe
                          select a_column,
                                 case when instr(upper(a_column),'CROSS') > 0 then a_column end has_cross,
                                 case when instr(replace(upper(a_column),',',' '),' CROSS ') > 0 then a_column end has_cross_word
                            from your_table
                          Regards

                          Etbin