Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2011
    Posts
    14

    Unanswered: SQL: Regexp_Like - matching individual letters?

    job title that contains an m followed at some point by an n followed
    at some point by a g

    BLAHBLAH
    WHERE REGEXP_LIKE
    (J.JOB_TITLE, '[M]{1,}[N]{1,}[G]{1,}','i');

    ======
    this isnt working as expected. it selects only two incorrect rows instead of 4 correct ones.

    whats the correct solution? this is my first time dealing with regular expressions in sql.

  2. #2
    Join Date
    Oct 2011
    Posts
    14
    i tried different variations including {1}, doesnt work.

  3. #3
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by vegan_toronto View Post
    this isnt working as expected. it selects only two incorrect rows instead of 4 correct ones.
    Show us rows that are expected to be returned by your query.
    The condition in regexp_like says:
    "return rows that contain one or more letter M followed by one or more N
    followed by one or more G"

    And oracle selects exactly what the query says:
    Code:
    with data as(
      select 'x mng y' title       from dual union all
      select 'x mmmmmnnnnnggggg x' from dual union all
      select 'x m n g x'           from dual union all
      select 'm_n_g'           from dual 
    )
    SELECT *
    FROM data
    WHERE regexp_like( title, '[M]{1,}[N]{1,}[G]{1,}','i');
      
    
    TITLE               
    ------------------- 
    x mng y             
    x mmmmmnnnnnggggg x

  4. #4
    Join Date
    Oct 2011
    Posts
    14
    oops, my bad. it supposed to return rows like these:

    Stock Manager, Sales Manager, Account Manager, Marketing Manager, Marketing Rep

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    In that case, LIKE might do the job:
    Code:
    where upper(title) like 'M%N%G%'

  6. #6
    Join Date
    Jan 2012
    Posts
    84
    For that case use this regexp pattern: M.*N.*G.

    Remember that using a regexp matching takes more time than simple LIKE operator,
    and prefer LIKE rather than regexp_like if possible
    Code:
    create table t12(
      name varchar2( 100 )
    );
    
    insert into t12
    select 'Stock Manager' from dual union
    select 'Sales Manager' from dual union
    Select 'Account Manager' from dual union
    select 'Marketing Manager' from dual
    union select 'Marketing Rep' from dual;
    
    insert into t12
    select table_name from all_tables
    cross join (
      select 1 from dual connect by level <= 1000
    );
    
    128000 rows created.
    
    SQL> set timing on
    
    SQL> select * from t12 where regexp_like( name, 'M.*N.*G', 'i' );
    
    NAME
    --------------------------------------------------------------------------------
    Account Manager
    Marketing Manager
    Marketing Rep
    Sales Manager
    Stock Manager
    
    Elapsed: 00:00:00.77
    
    SQL> select * from t12 where upper( name ) like '%M%N%G%';
    
    NAME
    --------------------------------------------------------------------------------
    Account Manager
    Marketing Manager
    Marketing Rep
    Sales Manager
    Stock Manager
    
    Elapsed: 00:00:00.10

  7. #7
    Join Date
    Oct 2011
    Posts
    14
    thank you Kordirko, that worked beautifully!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •