Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

DB2 CONCAT (Concatenate) Function

DZone's Guide to

DB2 CONCAT (Concatenate) Function

The DB2 CONCAT function will combine two separate expressions to form a single string expression.

· Big Data Zone
Free Resource

Need to build an application around your data? Learn more about dataflow programming for rapid development and greater creativity. 

The DB2 CONCAT function will combine two separate expressions to form a single string expression. It can leverage database fields, or explicitly defined strings as one or both expression when concatenating the values together.

The syntax for the CONCAT function is shown here:

-- Using Database Fields
SELECT CONCAT({field_1}, {field_2}) FROM {table_name};

-- Using String Expressions
SELECT CONCAT('{string_1}', '{string_2}') FROM {table_name};

-- Using Database Fields and String Expressions
SELECT CONCAT({field_1}, '{string_2}') FROM {table_name};


To show an example statement in use:

SELECT CONCAT(MFNAME, MFPARTNUMBER) FROM CATENTRY;


The SQL statement above will take and concatenate the values from the MFNAME and MFPARTNUMBER columns in the CATENTRY table.

If we want to add an explicit string (or space) between the two columns, we could leverage the following SQL statement to do so. This query will add a blank space between the two defined expressions.

SELECT CONCAT(CONCAT(MFNAME, ' '), MFPARTNUMBER) FROM CATENTRY;


NOTE: There is a shortcut notation available; however, I’ve found that if DB2 is not configured to enable it you will receive errors should you attempt to use it.

To leverage the CONCAT function shortcut, you’ll leverage a ‘||’ (double pipe) notation, and will result in an SQL query with the following syntax and will produce the same result as the second example query above:

SELECT MFNAME || ' ' || MFPARTNUMBER FROM CATENTRY;


If you want to read the IBM DB2 documentation on the CONCAT scalar function, please visit the DB2 v9.7 Information Center: CONCAT scalar function.

Check out the Exaptive data application Studio. Technology agnostic. No glue code. Use what you know and rely on the community for what you don't. Try the community version.

Topics:
architects ,bigdata ,tips and tricks ,big data

Published at DZone with permission of Drew Harvey, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}