Field name in LIKE statement

dasmidbforum · #1 (**permalink**) 09-14-06, 01:00

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

Littlefoot · #2 (**permalink**) 09-14-06, 01:07

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>

r937 · #3 (**permalink**) 09-14-06, 05:40

also no problem in mysql --

Code:

 where TABLE_B.f1 like concat('%', TABLE_A.f2, '%')

Tyveleyn · #4 (**permalink**) 09-14-06, 06:36

In Informix it can be done the same way as previously described for Oracle... Looks like standard SQL to me.

Internet Services

Web Development

Digital Marketing

Consumer Tech