CONCAT and SQL

Question

I have to search a DB2 database for a string that is a concatination of two columns. Is there a way to CONCAT the columns then use the combined string in the WHERE clause? I've searched but can't seem to find an answer to this.

Thanks

Answer 1

try

select * 
from MyTable
where Col1 concat Col2 = 'MySearchValue'

Answer 2

Something like

SELECT *, CONCAT(column1, column2) AS s FROM table WHERE column3 = s

Answer 3

After a bit more searching in the IBM manuals I came up with this solution that works for me;

Select
 Col1 || Col2 as MyCol1
Where CONCAT(RTRIM(Col1),(RTRIM(Col2)) = 'searchvalue'

Thanks for the help

Answer 4

The simplest solution works, no need to use CONCAT().

 SELECT Col1 || Col2 as MyCol1 
 FROM MY_TABLE
 WHERE Col1 || Col2 = 'searchvalue'

Just beware that the search will not be indexed. If you want it to be indexed, you have to use an index based on a function. Then 2 possibilities :

1. The DB engine supports it (I don't think that DB2 does)
2. Do it yourself : use an extra indexed column updated via triggers on INSERT & UPDATE.