CODEWITHSUNDEEP
sqldb2concatenation
Pwnstar
Pwnstar

Reputation: 2245

How to use a column named as keyword in a DB2 concat function

So I got this table FOO which has a column of the type VARCHAR and is named COMMENT (which happens to be a reserved keyword). When I am trying to use it in a CONCAT function in my select the result is NULL.

How can I fix this?

SELECT 
    CONCAT(CONCAT(CONCAT(CONCAT('{"NAME":"', NAME), '","COMMENT":"'), COMMENT),'"}')
FROM
    SOMESCHEMA.FOO

I also tried to use " or ' around COMMENT, but then it is interpreted as a VARCHAR...

2nd I used ` but that happens to print me the following error.

  1. [Code: -104, SQL State: 42601] ILLEGAL SYMBOL "`". SOME SYMBOLS THAT MIGHT BE LEGAL ARE:.

I also tried to add the SCHEMA and the TABLE name in front of the column like:

CONCAT(CONCAT(CONCAT(CONCAT('{"NAME":"', NAME), '","COMMENT":"'), SOMESCHEMA.FOO.COMMENT),'"}')

But no luck.

Upvotes: 0

Views: 255

Answers (2)

jarlh
jarlh

Reputation: 44766

It seems like DB2 also accept the ANSI/ISO SQL || concatenation:

SELECT 
    '{"NAME":"' || NAME || '","COMMENT":"' || COMMENT || '"}'
FROM
    SOMESCHEMA.FOO

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269673

Did you try this?

SELECT CONCAT(CONCAT(CONCAT(CONCAT('{"NAME":"', NAME
                                  ), '","COMMENT":"'
                           ), "COMMENT"
                    ),
              '"}')
FROM SOMESCHEMA.FOO

That is, double quotes only around the column name.

I would find this simpler to read using the infix operator:

SELECT '{"NAME":"' CONCAT NAME CONCAT '","COMMENT":"' CONCAT "COMMENT" CONCAT '"}'
FROM SOMESCHEMA.FOO

or:

SELECT '{"NAME":"' || NAME || '","COMMENT":"' || "COMMENT" || '"}'
FROM SOMESCHEMA.FOO

Upvotes: 1

Related Questions