1 2 Previous Next 16 Replies Latest reply: Dec 6, 2012 11:40 AM by jihuyao RSS

    using range of values in like

    vai
      suppose i have a table values as-

      CNAME
      -------------------------
      Hoffman
      Giovanni
      Liu
      Grass
      Clemens
      Pereira
      Cisneros

      Now, i want to list out those names whose name contains alphabets between 'a' and 'g'.
      How can i achieve this without using regular expression?
        • 1. Re: using range of values in like
          hm
           
          select cname 
          from yourtable
          where (cname like '%a%' or 
                     cname like '%b%' or
                     ... or
                     cname like '%g%');
          or
          select cname from yourtable
          where translate(cname,'abcdefg','xxxxxxx')!=cname;
          Edited by: hm on 06.12.2012 05:06
          • 2. Re: using range of values in like
            920059
            Hello,

            One way to do it without using regexp's is to write a script that builds string of sql with the needed filters for dynamic execution.
            • 3. Re: using range of values in like
              vijayrsehgal-Oracle
              Hope the below is what you are looking for.
              CREATE TABLE TESTME
              (
                COL1  VARCHAR2(100 BYTE)
              );
              
              insert into testme values('Hoffman' );
              
              insert into testme values('Giovanni' );
              
              insert into testme values('Liu');
              
              insert into testme values('Grass' );
              
              insert into testme values('Clemens' );
              
              insert into testme values('Pereira' );
              
              insert into testme values('Cisneros' );
              
              select * from testme where lower(col1) like '%a%' or lower(col1) like '%g%';
              • 4. Re: using range of values in like
                vai
                but what if the range is large say a-q, it would be time consuming.
                Is there any alternate solution?
                • 5. Re: using range of values in like
                  hm
                  -- testdata:
                  with yourtable as
                  (
                    select 'Hoffman' cname from dual union all
                    select 'Giovanni' from dual union all
                    select 'Liu' from dual union all
                    select 'Grass' from dual union all
                    select 'Clemens' from dual union all
                    select 'Pereira' from dual union all
                    select 'Cisneros' from dual
                  )  
                  -- query:
                  select cname,  translate(cname,'abcdefg','xxxxxxx')
                  from yourtable
                  where  translate(cname,'abcdefg','xxxxxxx')!=cname;
                  • 6. Re: using range of values in like
                    Solomon Yakobson
                    vaibhav wrote:
                    but what if the range is large say a-q, it would be time consuming.
                    Is there any alternate solution?
                    You could use TRANSLATE:
                    SQL> select  *
                      2    from  testme
                      3    where translate(col1,'abcdefg','xxxxxxx') != col1
                      4  /
                    
                    COL1
                    ----------------------------------------------------------------
                    Hoffman
                    Giovanni
                    Grass
                    Clemens
                    Pereira
                    Cisneros
                    
                    6 rows selected.
                    
                    SQL> 
                    SY.
                    • 7. Re: using range of values in like
                      vai
                      is translate a ansi sql function or oracle standards string function??
                      • 8. Re: using range of values in like
                        6363
                        vaibhav wrote:
                        but what if the range is large say a-q, it would be time consuming.
                        Is there any alternate solution?
                        Regular expressions.

                        But you don't want to use that. How many alternate ways of doing the same thing do you want, and how many other functions and features are you refusing to use and why?
                        • 9. Re: using range of values in like
                          BluShadow
                          translate is an oracle function.
                          • 10. Re: using range of values in like
                            vai
                            Because i want the solution using ansi sql functions.
                            • 11. Re: using range of values in like
                              Solomon Yakobson
                              TRANSLATE is Oracle supplied function. AFAIK it exists in DB2, Postgres but doesn't exist in mySQL and SQL Server.

                              SY.
                              • 12. Re: using range of values in like
                                BluShadow
                                vaibhav wrote:
                                Because i want the solution using ansi sql functions.
                                Then you'll have to use lots of LIKE statements as already given. LIKE is ANSI compliant.

                                Let me guess, you're writing a 'generic' application that can work against any database engine? Oh dear.
                                • 13. Re: using range of values in like
                                  vijayrsehgal-Oracle
                                  CREATE TABLE TESTME
                                  (
                                    COL1  VARCHAR2(100 BYTE)
                                  );
                                  
                                  insert into testme values('Hoffman' );
                                  
                                  insert into testme values('Giovanni' );
                                  
                                  insert into testme values('Liu');
                                  
                                  insert into testme values('Grass' );
                                  
                                  insert into testme values('Clemens' );
                                  
                                  insert into testme values('Pereira' );
                                  
                                  insert into testme values('Cisneros' );
                                  
                                  insert into testme values('zzz');
                                  
                                  insert into testme values('r');
                                  
                                  
                                  
                                  
                                  
                                  select translate(lower(col1),'rstuvwxyz','1'),col1 from testme where replace(translate(lower(col1),'rstuvwxyz','1'),'1','') is not null
                                  • 14. Re: using range of values in like
                                    6363
                                    vaibhav wrote:
                                    Because i want the solution using ansi sql functions.
                                    Regular expressions are ANSI standard

                                    http://en.wikipedia.org/wiki/SQL#Standardization

                                    >
                                    1999      SQL:1999      SQL3      Added regular expression matching, recursive queries (e.g. transitive closure), triggers, support for procedural and control-of-flow statements, non-scalar types, and some object-oriented features (e.g. structured types). Support for embedding SQL in Java (SQL/OLB) and vice-versa (SQL/JRT).
                                    1 2 Previous Next