1

I want to use something like:

SELECT A.* FROM MYSCHEMA.MYTABLE A, MYSCHEMA.SECONDTAB B WHERE A.COL1 LIKE B.COL1 || '%'

How to achieve this in DB2 ?

Similar question but without an answer

Community
  • 1
  • 1
Vicky
  • 16,679
  • 54
  • 139
  • 232
  • Please always explicitly qualify your joins, don't use the implicit-join syntax (comma-separated `FROM` clause). For one thing, it makes finding the correlation conditions much easier for humans (and doesn't really affect computers). It also makes dealing with `LEFT JOIN`s easier. – Clockwork-Muse Feb 05 '13 at 17:11
  • I see 3 answers in that question. And your code has no `IN`. Why did put it in the title? – ypercubeᵀᴹ Feb 07 '13 at 07:32
  • What is the data type of column COL1 in each of the two tables involved? – Fred Sobotka Feb 11 '13 at 19:56
  • @FredSobotka: Varchar is the datatype for all the columns. – Vicky Feb 12 '13 at 10:23

1 Answers1

3

Not so sure what you try to achieve but it works in DB2. Just add TRIM function at LIKE TRIM(B.COL1) || '%' or swap it to WHERE B.COL1 LIKE TRIM(A.COL1) || '%'

[edit] Above works in DB2 in as/400(or whatever name they call it right now)

Below might work with DB2 LUW (tested with DB2 windows v8.2), assuming COL1 type = varchar

WHERE SUBSTR(A.COL1,1,LENGTH(A.COL1)) = SUBSTR( B.COL1, 1,LENGTH(A.COL1))
lamLam
  • 455
  • 2
  • 8
  • Not working! Getting error Message: A LIKE predicate or POSSTR scalar function is not valid because the first operand is not a string expression or the second operand is not a string. A LOCATE or POSITION scalar function is not valid because the first operand is not a string or the second operand is not a string expression. – Vicky Feb 06 '13 at 03:28
  • @Nikunj Chauhan, Please look at updated answer. Sorry, initially i just test with DB2 in as/400 and thought it works with other DB2 flavour. Probably others have better/easier answer – lamLam Feb 07 '13 at 07:30
  • 1
    Both queries work on DB2 LUW v9.7. In the second query, the first SUBSTR expression is kind of pointless because SUBSTR of A.COL1 from 1 to the length of A.COL1 is an expensive way of saying A.COL1 – Fred Sobotka Feb 13 '13 at 23:04
  • @Fred Sobotka. you are right, the first SUBSTR is useless and thanks for checking on v9.7. – lamLam Feb 14 '13 at 00:51