Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2006
    Posts
    1

    Unanswered: Field name in LIKE statement

    Hi,

    I have come across an SQL statement today that I actually thought was impossible. I always thought that a LIKE statement must use a literal value such as field1 like '%ABC'. But today I came across a subselect that used a like statement

    select * from TABLE_A
    where exists (select 'X' from TABLE_B where TABLE_B.f1 like '%'+TABLE_A.f2+'%')

    As I said I didn't think you could use a field in a like statement. This works in SQL Server,,, could people tell me if this would function for other databases.

    Thanks
    Dave

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yes; no problem in Oracle.
    Code:
    SQL> SELECT * FROM EMP e
      2  WHERE EXISTS (SELECT NULL FROM DEPT d
      3                WHERE d.deptno LIKE '%' || e.sal || '%');
    
         EMPNO ENAME      JOB              MGR HIREDATE        SAL     DEPTNO
    ---------- ---------- --------- ---------- -------- ---------- ----------
          7839 KING       PRESIDENT            17.11.81         50         10
    
    SQL>

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    also no problem in mysql --
    Code:
     where TABLE_B.f1 like concat('%', TABLE_A.f2, '%')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    In Informix it can be done the same way as previously described for Oracle... Looks like standard SQL to me.

  5. #5
    Join Date
    Jun 2012
    Posts
    1
    I LOVE you man!!

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Some systems do have restrictions on the pattern for the LIKE predicate, allowing only constant expressions. That permits additional optimizations like precompiling the pattern. I guess this would be the reason why you were not aware that a variable pattern can also be used.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Quote Originally Posted by dasmidbforum View Post
    could people tell me if this would function for other databases.
    Yes! (As long as the dbms supports ANSI/SQL.)

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by JarlH View Post
    Yes! (As long as the dbms supports ANSI/SQL.)
    no!

    because very few other database systems use the plus sign for concatenation --
    Code:
    where TABLE_B.f1 like '%'+TABLE_A.f2+'%'
    you would have to use the double-pipes concatenation as in post #2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Quote Originally Posted by r937 View Post
    no!

    because very few other database systems use the plus sign for concatenation --
    Code:
    where TABLE_B.f1 like '%'+TABLE_A.f2+'%'
    you would have to use the double-pipes concatenation as in post #2
    You are absolutely right, plus sign for concatenation isn't ANSI SQL.

    (I was only considering "col1 like col2" when answering. What can I say, first day at work after vacation...)

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    LIKE pattern including a column reference is an optional feaure(F281 LIKE enhancements) of ISO/ANSI 2003.


    Mimer SQL Developers - Mimer SQL-2003 Validator
    Code:
    Mimer SQL-2003 Validator
    
    Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003. 
    
    The SQL-2003 Validator!
    
    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button: 
    
    SELECT e.*
     FROM  employee  e
     INNER JOIN
           deparment d
      ON   d.deptname LIKE '%' || e.midinit || '%'
    ; 
     
      See Validator Examples  
     
    Result:
    
    The following feature outside Core SQL-2003 is used:
    
    F281, "LIKE enhancements"

Posting Permissions

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