CODEWITHSUNDEEP
sqldb2
Krisshan Sirohiya
Krisshan Sirohiya

Reputation: 69

Show special characters in concat with db2 sql

I am running query like this for db2 sql

SELECT CONCAT(CONCAT('Order_no is =', Order_no), ' for line') FROM orders;

And result is coming like this:

Order_no is =123456 for line

But I want to fetch result as

Order_no is ='6640354' for line

I need to apply special characters to output, so can you please help me in this.

Upvotes: 1

Views: 6615

Answers (4)

Mustafa DOGRU
Mustafa DOGRU

Reputation: 4112

You can also use this;

select 'Order_no is=''' || trim(Order_no) || ''' for line' from orders;

Upvotes: 2

CRPence
CRPence

Reputation: 1259

Not sure why the use of nested CONCAT scalar is shown so pervasively in db2-tagged discussions, to concatenate more than one value.? Perhaps caused by how sometimes the documentation separates expressions and scalar functions, and in the latter docs might only offer a tiny Note: 'The CONCAT function is identical to the CONCAT operator. For more information, see "Expressions".'
I personally find the following use of the CONCAT operator, to be a much more readable way to compose the same character-string expression:

'Order_no is =''' CONCAT Order_no CONCAT ''' for line'

Upvotes: 0

Rahul
Rahul

Reputation: 77866

You can escape special character using \ or using another single quote like

select CONCAT( CONCAT('Order_no is =\'', Order_no), '\' for line') from orders;

Check DB2 documentation on Escaping special characters

Upvotes: -1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Use two single quotes together to escape a single quote:

SELECT CONCAT(CONCAT('Order_no is =''', Order_no), ''' for line')
FROM orders;

Upvotes: 2

Related Questions