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

Hortonworks Sandbox for HDP and HDF is your chance to get started on learning, developing, testing and trying out new features. Each download comes preconfigured with interactive tutorials, sample data and developments from the Apache community.

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.

Hortonworks Community Connection (HCC) is an online collaboration destination for developers, DevOps, customers and partners to get answers to questions, collaborate on technical articles and share code examples from GitHub.  Join the discussion.

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

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}