Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Combine colums on a DB2 table

There are three separate columns on a DB2 table that have to match the corresponding input info that I have to pull off the rows I need. The input data is combined into one element. Can the three columns be combined into one in the select statement.

You can use, in SQL, the "concat" function to join several fields. For instance: Select ofr concat ofc concat dpt as root from myFile will give you 1 field. Please note that if a field is numerical, you will need to specify digits(yourField) if you want to keep the leading zeros. If you just want a trimmed field without the leading zeros, you can use char(yourField).

If one record has the following information:
OFR (1 alpha): L
OFC (1 alpha): L
DPT (2 alpha): LF
UNQ (6 packed):1234

The result of ofr concat ofc concat dpt concat digits(unq) would be LLLF001234 while if you use char(unq), you will get LLLF1234.

You can use concat, digits and char in other places than select. You can use it in the where and having statement as well.


Search400's targeted search engine: Get relevant information on DB2/400.

The Best Web Links: tips, tutorials and more.

Check out this online event, Getting the Most out of SQL & DB2 UDB for the iSeries.

Dig Deeper on DB2 UDB (universal databases)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.